Range limited by a wildcard

[Guest]

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,

 

[RagDyeR]

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!
==============================================



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,

 

[Guest]

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

*

 

[Guest]

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]

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?

 

[Guest]

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]

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.