|
Range limited by a wildcard
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, |
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, |
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, |
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, |
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, |
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, |
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, |
| All times are GMT +1. The time now is 09:43 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com