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" <[email protected]> wrote in message
news:[email protected]...

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" <[email protected]> wrote in message
> news:[email protected]...
>
> 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" <[email protected]> wrote in message
> news:[email protected]...
>
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> >
> > 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > >
> > > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > >
> > > > 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,
> > > >
> > > >
> > > >
> >
> >