Determine if Cell Address is within a Range
 

I'm trying to create a conditional format that will highlight a cell if
two conditions are met one of which is that the cell is found within a
dynamic range.

Is there a function or formula I could use to determine if, say, cell
B1 is within the range A1:D1? If so, I could use a conditional format
like the following:

= AND(B1=Min(E1:E5), B1 is in the Range A1:D1)

It may look funny but there are certain times that the range will
change from A1:D1 to C1 or B1 based on the data entered elsewhere in
the table.

Thanks.

- John

Determine if Cell Address is within a Range
 

It CAN be done...Here's what I did:

1)
A12: a1
A13: a5

2)I created a dynamic range called rngTest
which refers to: =INDIRECT(Sheet1!$A$12&":"&Sheet1!$A$13)

3)Populate E1:E5 with numbers

4) G1: =AND(B1=MIN(E1:E5), ISNUMBER(ROW(B1 rngTest)))

Note the space between B1 and rngTest....that attempts to create an
intersection between B1 and the range rngTest. If there's no interesection,
then there's no row number.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"John Michl" wrote:

I'm trying to create a conditional format that will highlight a cell if
two conditions are met one of which is that the cell is found within a
dynamic range.

Is there a function or formula I could use to determine if, say, cell
B1 is within the range A1:D1? If so, I could use a conditional format
like the following:

= AND(B1=Min(E1:E5), B1 is in the Range A1:D1)

It may look funny but there are certain times that the range will
change from A1:D1 to C1 or B1 based on the data entered elsewhere in
the table.

Thanks.

- John

One more thing...CF can't use Unions so..
 

If you want to use a Conditional Format....
You'd need to put the formula in a cell and have the CF cell's Formula Is
refer to that cell.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Ron Coderre" wrote:

It CAN be done...Here's what I did:

1)
A12: a1
A13: a5

2)I created a dynamic range called rngTest
which refers to: =INDIRECT(Sheet1!$A$12&":"&Sheet1!$A$13)

3)Populate E1:E5 with numbers

4) G1: =AND(B1=MIN(E1:E5), ISNUMBER(ROW(B1 rngTest)))

Note the space between B1 and rngTest....that attempts to create an
intersection between B1 and the range rngTest. If there's no interesection,
then there's no row number.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"John Michl" wrote:

I'm trying to create a conditional format that will highlight a cell if
two conditions are met one of which is that the cell is found within a
dynamic range.

Is there a function or formula I could use to determine if, say, cell
B1 is within the range A1:D1? If so, I could use a conditional format
like the following:

= AND(B1=Min(E1:E5), B1 is in the Range A1:D1)

It may look funny but there are certain times that the range will
change from A1:D1 to C1 or B1 based on the data entered elsewhere in
the table.

Thanks.

- John

Determine if Cell Address is within a Range
 

First, don't change subject lines. Doing so screws up some newsreaders.

Ron Coderre wrote...
If you want to use a Conditional Format....
You'd need to put the formula in a cell and have the CF cell's Formula Is
refer to that cell.
....

You were using an intersection, not a union. So don't use either. Excel
treats colons, :, as operators for range references, returning
references to the smallest single area range containing all the range
references separated by the colons. For example, if you had a defined
name RNG referring to C5:J5, the expression RNG:H12 would result in a
reference to C5:J12. Conditional formatting has no problem with such
range references, so use

=AND(B1=MIN(E1:E5),ROWS(RNG:B1)=ROWS(RNG),
COLUMNS(RNG:B1)=COLUMNS(RNG))

Note that this also means Excel has no trouble with multiple cell
references that look like

Sheet1!A1:Sheet1!X99

as long as the worksheet name is the same.

Determine if Cell Address is within a Range
 

Actually, I meant to put UNIONS,INTERSECTIONS etc... (referring to the error
message Excel gives)...
BUT, since I didn't---Thanks for clarifying, Harlan

Regarding my proposed solution, thanks (again) for taking the time to
perfect it.

***********
Regards,
Ron

XL2002, WinXP-Pro


"Harlan Grove" wrote:

First, don't change subject lines. Doing so screws up some newsreaders.

Ron Coderre wrote...
If you want to use a Conditional Format....
You'd need to put the formula in a cell and have the CF cell's Formula Is
refer to that cell.
....

You were using an intersection, not a union. So don't use either. Excel
treats colons, :, as operators for range references, returning
references to the smallest single area range containing all the range
references separated by the colons. For example, if you had a defined
name RNG referring to C5:J5, the expression RNG:H12 would result in a
reference to C5:J12. Conditional formatting has no problem with such
range references, so use

=AND(B1=MIN(E1:E5),ROWS(RNG:B1)=ROWS(RNG),
COLUMNS(RNG:B1)=COLUMNS(RNG))

Note that this also means Excel has no trouble with multiple cell
references that look like

Sheet1!A1:Sheet1!X99

as long as the worksheet name is the same.