Determine if Cell Address is within a Range

[John Michl]

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 A11? If so, I could use a conditional format
like the following:

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

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

Thanks.

- John

 

[Guest]

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

 

[Guest]

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

 

[Harlan Grove]

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.

 

[Guest]

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