Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]() I need to run a formula that would do this on an active cell: =MIN(DATEVALUE(SUBSTITUTE('408134.xls'!K10:K11,"." ,"/"))) But instead of delimiting the range from K10:K11, I would like the formula to evaluate the column K with a range defined within two wildcards *, located in column B. The upper range would be * The lower range would also be * This would be continuous, therefore the formula needs to go the next range delimited by two wildcards * Regards, |
#2
![]() |
|||
|
|||
![]()
When you say wildcard, I'm assuming B1 is start of date range (K10 or
whatever), And B2 is end of date range (K11 or whatever). =MIN(DATEVALUE(SUBSTITUTE(INDIRECT(B1&":"&B2),".", "/"))) Although you didn't mention it, since it's your formula, you know that it's an *array* formula, but for the sake of other readers: Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Jeff" wrote in message ... I need to run a formula that would do this on an active cell: =MIN(DATEVALUE(SUBSTITUTE('408134.xls'!K10:K11,"." ,"/"))) But instead of delimiting the range from K10:K11, I would like the formula to evaluate the column K with a range defined within two wildcards *, located in column B. The upper range would be * The lower range would also be * This would be continuous, therefore the formula needs to go the next range delimited by two wildcards * Regards, |
#3
![]() |
|||
|
|||
![]()
hI RD,
I apologize if I wasn't clear. Here's an example: The wildcards are in Column B; the dates that I need to evaluate are in column K. I need to find the oldest date in K within the 2 wildcards. Regards, * Prov 2/28/2005 Prov 1/31/2005 * "RagDyeR" wrote: When you say wildcard, I'm assuming B1 is start of date range (K10 or whatever), And B2 is end of date range (K11 or whatever). =MIN(DATEVALUE(SUBSTITUTE(INDIRECT(B1&":"&B2),".", "/"))) Although you didn't mention it, since it's your formula, you know that it's an *array* formula, but for the sake of other readers: Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Jeff" wrote in message ... I need to run a formula that would do this on an active cell: =MIN(DATEVALUE(SUBSTITUTE('408134.xls'!K10:K11,"." ,"/"))) But instead of delimiting the range from K10:K11, I would like the formula to evaluate the column K with a range defined within two wildcards *, located in column B. The upper range would be * The lower range would also be * This would be continuous, therefore the formula needs to go the next range delimited by two wildcards * Regards, |
#4
![]() |
|||
|
|||
![]()
Hi RD,
I apologize if I wasn't clear,. Here's an example: I need a formula that would evaluate the oldest dates in column K within 2 wildcards located in column B. * Prov 2/28/2005 Prov 1/31/2005 * "RagDyeR" wrote: When you say wildcard, I'm assuming B1 is start of date range (K10 or whatever), And B2 is end of date range (K11 or whatever). =MIN(DATEVALUE(SUBSTITUTE(INDIRECT(B1&":"&B2),".", "/"))) Although you didn't mention it, since it's your formula, you know that it's an *array* formula, but for the sake of other readers: Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Jeff" wrote in message ... I need to run a formula that would do this on an active cell: =MIN(DATEVALUE(SUBSTITUTE('408134.xls'!K10:K11,"." ,"/"))) But instead of delimiting the range from K10:K11, I would like the formula to evaluate the column K with a range defined within two wildcards *, located in column B. The upper range would be * The lower range would also be * This would be continuous, therefore the formula needs to go the next range delimited by two wildcards * Regards, |
#5
![]() |
|||
|
|||
![]()
Are you saying that if there is an asterisk in B8, and another in B24, that
you want the max date in Column K from within the range of K8 to K24? If that's so, how do the asterisks get there? Do you key them in, or are they the results of other formulas? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Jeff" wrote in message ... Hi RD, I apologize if I wasn't clear,. Here's an example: I need a formula that would evaluate the oldest dates in column K within 2 wildcards located in column B. * Prov 2/28/2005 Prov 1/31/2005 * "RagDyeR" wrote: When you say wildcard, I'm assuming B1 is start of date range (K10 or whatever), And B2 is end of date range (K11 or whatever). =MIN(DATEVALUE(SUBSTITUTE(INDIRECT(B1&":"&B2),".", "/"))) Although you didn't mention it, since it's your formula, you know that it's an *array* formula, but for the sake of other readers: Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Jeff" wrote in message ... I need to run a formula that would do this on an active cell: =MIN(DATEVALUE(SUBSTITUTE('408134.xls'!K10:K11,"." ,"/"))) But instead of delimiting the range from K10:K11, I would like the formula to evaluate the column K with a range defined within two wildcards *, located in column B. The upper range would be * The lower range would also be * This would be continuous, therefore the formula needs to go the next range delimited by two wildcards * Regards, |
#6
![]() |
|||
|
|||
![]()
Hi RD,
1 - Yes. This is what I need. 2 - asterisks get there after an extraction of a file in SAP. 3 - Do you key them in, or are they the results of other formulas? No Thanks, JF "Ragdyer" wrote: Are you saying that if there is an asterisk in B8, and another in B24, that you want the max date in Column K from within the range of K8 to K24? If that's so, how do the asterisks get there? Do you key them in, or are they the results of other formulas? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Jeff" wrote in message ... Hi RD, I apologize if I wasn't clear,. Here's an example: I need a formula that would evaluate the oldest dates in column K within 2 wildcards located in column B. * Prov 2/28/2005 Prov 1/31/2005 * "RagDyeR" wrote: When you say wildcard, I'm assuming B1 is start of date range (K10 or whatever), And B2 is end of date range (K11 or whatever). =MIN(DATEVALUE(SUBSTITUTE(INDIRECT(B1&":"&B2),".", "/"))) Although you didn't mention it, since it's your formula, you know that it's an *array* formula, but for the sake of other readers: Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Jeff" wrote in message ... I need to run a formula that would do this on an active cell: =MIN(DATEVALUE(SUBSTITUTE('408134.xls'!K10:K11,"." ,"/"))) But instead of delimiting the range from K10:K11, I would like the formula to evaluate the column K with a range defined within two wildcards *, located in column B. The upper range would be * The lower range would also be * This would be continuous, therefore the formula needs to go the next range delimited by two wildcards * Regards, |
#7
![]() |
|||
|
|||
![]()
Sorry for the delay in replying, but I just had to get in some Spring skiing
before Mammoth turned into it's customary "mashed potatoes" consistency. <"asterisks get there after an extraction of a file in SAP" Don't really understand this, but this *array* formula worked for me when I tested with keyed in asterisks. Also, if there were more then 2 asterisks, the first and last set the range limits. =MIN(DATEVALUE(SUBSTITUTE(INDIRECT("K"&MATCH("~*", B1:B100,0)&":K"&MATCH("~*" ,B1:B100)),".","/"))) Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Jeff" wrote in message ... Hi RD, 1 - Yes. This is what I need. 2 - asterisks get there after an extraction of a file in SAP. 3 - Do you key them in, or are they the results of other formulas? No Thanks, JF "Ragdyer" wrote: Are you saying that if there is an asterisk in B8, and another in B24, that you want the max date in Column K from within the range of K8 to K24? If that's so, how do the asterisks get there? Do you key them in, or are they the results of other formulas? -- Regards, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Jeff" wrote in message ... Hi RD, I apologize if I wasn't clear,. Here's an example: I need a formula that would evaluate the oldest dates in column K within 2 wildcards located in column B. * Prov 2/28/2005 Prov 1/31/2005 * "RagDyeR" wrote: When you say wildcard, I'm assuming B1 is start of date range (K10 or whatever), And B2 is end of date range (K11 or whatever). =MIN(DATEVALUE(SUBSTITUTE(INDIRECT(B1&":"&B2),".", "/"))) Although you didn't mention it, since it's your formula, you know that it's an *array* formula, but for the sake of other readers: Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Jeff" wrote in message ... I need to run a formula that would do this on an active cell: =MIN(DATEVALUE(SUBSTITUTE('408134.xls'!K10:K11,"." ,"/"))) But instead of delimiting the range from K10:K11, I would like the formula to evaluate the column K with a range defined within two wildcards *, located in column B. The upper range would be * The lower range would also be * This would be continuous, therefore the formula needs to go the next range delimited by two wildcards * Regards, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
create range bar graph | Charts and Charting in Excel | |||
Dynamic Print Range Help | Excel Worksheet Functions | |||
Define a range based on another named range | Excel Worksheet Functions | |||
named range refers to: in a chart | Excel Discussion (Misc queries) | |||
range name | Excel Worksheet Functions |