software

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.

- JohnIt 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$12amp;quot;:quot;amp;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-Proquot;John Michlquot; wrote:

gt; I'm trying to create a conditional format that will highlight a cell if
gt; two conditions are met one of which is that the cell is found within a
gt; dynamic range.
gt;
gt; Is there a function or formula I could use to determine if, say, cell
gt; B1 is within the range A11? If so, I could use a conditional format
gt; like the following:
gt;
gt; = AND(B1=Min(E1:E5), B1 is in the Range A11)
gt;
gt; It may look funny but there are certain times that the range will
gt; change from A11 to C1 or B1 based on the data entered elsewhere in
gt; the table.
gt;
gt; Thanks.
gt;
gt; - John
gt;
gt;

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-Proquot;Ron Coderrequot; wrote:

gt; It CAN be done...Here's what I did:
gt;
gt; 1)
gt; A12: a1
gt; A13: a5
gt;
gt; 2)I created a dynamic range called rngTest
gt; which refers to: =INDIRECT(Sheet1!$A$12amp;quot;:quot;amp;Sheet1!$A$13)
gt;
gt; 3)Populate E1:E5 with numbers
gt;
gt; 4) G1: =AND(B1=MIN(E1:E5), ISNUMBER(ROW(B1 rngTest)))
gt;
gt; Note the space between B1 and rngTest....that attempts to create an
gt; intersection between B1 and the range rngTest. If there's no interesection,
gt; then there's no row number.
gt;
gt; Does that help?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;John Michlquot; wrote:
gt;
gt; gt; I'm trying to create a conditional format that will highlight a cell if
gt; gt; two conditions are met one of which is that the cell is found within a
gt; gt; dynamic range.
gt; gt;
gt; gt; Is there a function or formula I could use to determine if, say, cell
gt; gt; B1 is within the range A11? If so, I could use a conditional format
gt; gt; like the following:
gt; gt;
gt; gt; = AND(B1=Min(E1:E5), B1 is in the Range A11)
gt; gt;
gt; gt; It may look funny but there are certain times that the range will
gt; gt; change from A11 to C1 or B1 based on the data entered elsewhere in
gt; gt; the table.
gt; gt;
gt; gt; Thanks.
gt; gt;
gt; gt; - John
gt; gt;
gt; gt;

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

Ron Coderre wrote...
gt;If you want to use a Conditional Format....
gt;You'd need to put the formula in a cell and have the CF cell's Formula Is
gt;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.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-Proquot;Harlan Grovequot; wrote:

gt; First, don't change subject lines. Doing so screws up some newsreaders.
gt;
gt; Ron Coderre wrote...
gt; gt;If you want to use a Conditional Format....
gt; gt;You'd need to put the formula in a cell and have the CF cell's Formula Is
gt; gt;refer to that cell.
gt; ....
gt;
gt; You were using an intersection, not a union. So don't use either. Excel
gt; treats colons, :, as operators for range references, returning
gt; references to the smallest single area range containing all the range
gt; references separated by the colons. For example, if you had a defined
gt; name RNG referring to C5:J5, the expression RNG:H12 would result in a
gt; reference to C5:J12. Conditional formatting has no problem with such
gt; range references, so use
gt;
gt; =AND(B1=MIN(E1:E5),ROWS(RNG:B1)=ROWS(RNG),
gt; COLUMNS(RNG:B1)=COLUMNS(RNG))
gt;
gt; Note that this also means Excel has no trouble with multiple cell
gt; references that look like
gt;
gt; Sheet1!A1:Sheet1!X99
gt;
gt; as long as the worksheet name is the same.
gt;
gt;