Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Jeff
 
Posts: n/a
Default 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,

  #2   Report Post  
RagDyeR
 
Posts: n/a
Default

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   Report Post  
Jeff
 
Posts: n/a
Default

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   Report Post  
Jeff
 
Posts: n/a
Default

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   Report Post  
Ragdyer
 
Posts: n/a
Default

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   Report Post  
Jeff
 
Posts: n/a
Default

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   Report Post  
Ragdyer
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
create range bar graph Aussie1497 Charts and Charting in Excel 2 April 26th 23 12:47 PM
Dynamic Print Range Help waxwing Excel Worksheet Functions 2 February 21st 05 04:47 PM
Define a range based on another named range Basil Excel Worksheet Functions 2 February 21st 05 02:47 PM
named range refers to: in a chart Spencer Hutton Excel Discussion (Misc queries) 1 December 14th 04 11:15 PM
range name Pedro Excel Worksheet Functions 0 November 9th 04 07:22 PM


All times are GMT +1. The time now is 10:29 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"