Conditional Formatting using Named Range

[timo4476]

Hi,

I know the answer to this is probably easy, but it's been driving me crazy trying to figure it out! I have a range of data on Sheet1 (call it A1:A10) that needs to be conditionally formatted based on whether or not the value in each cell is less or greater than the value in a cell on Sheet2. The range in Sheet2 will be the same corresponding cells as Sheet1 and A1 on Sheet1 will be compared to A1 on Sheet2, A2 compared to A2 and so on. I don't need these to be dynamic, as the list won't grow. I know that I need to use a Named Range since I'm using Excel 2007 so I can't reference another Sheet, but I can't figure out how to do this without making each individual cell on Sheet 2 it's own named Range. I'd like to have one named range that knows as I copy the formula down to compare each row to it's corresponding row in the named range. I know a helper column would be the easiest solution to this, but the data on Sheet2 is restricted and the audience for this report can't see it and I've been hiding and locking the columns, but that isn't a great solution and it's causing it's own trouble. If I can get this named range method to work, then I think I can just hide and lock the Sheet 2 tab.

Hopefully this makes sense and there is any easy solution!

Thanks for your help!

 

[circledchicken]

Hi,

Maybe try select cells A1:A10 on the Sheet1 (with A1 as the active cell), and create your greater than / less than rules based on something like this:

=INDEX(rngB, ROWS(A$1:A1))

where rngB refers to: =Sheet2!$A$1:$A$10

 

[Marcelo Branco]

For greater than try this

Select A1:A10

Use the formula option and insert this formula
=$A1>INDEX(MyRange,ROWS($A$1:A1))
pick the format you want

Adjust the name of the range (blue)

M.

 

[timo4476]

THANK YOU - That worked perfectly Marcelo!!

 

[markmzz]

A small modification in Marcelo's formula:

=$A1>INDEX(MyRange,ROW($A1))

Markmzz

 

[Marcelo Branco]

THANK YOU - That worked perfectly Marcelo!!

You are welcome and thanks for the feedback

M.

 

[Marcelo Branco]

A small modification in Marcelo's formula:

=$A1>INDEX(MyRange,ROW($A1))

Markmzz

Hi Mark,

I prefer to use ROWS($A$1:A1) because if someone insert a new row above the range, the formula still works correctly.

M.

 

[markmzz]

Hi Mark,

I prefer to use ROWS($A$1:A1) because if someone insert a new row above the range, the formula still works correctly.

M.

Hi Marcelo,

I did that for this text of the user "The range in Sheet2 will be the same corresponding cells as Sheet1".

Anyway, the problem was solved.

Markmzz

 

[circledchicken]

Hi Marcelo,

I did that for this text of the user "The range in Sheet2 will be the same corresponding cells as Sheet1".

Anyway, the problem was solved.

Markmzz

Hi Markmzz,

I agree with Marcelo in preferring the ROWS(..) construct.
It works with the OPs requirement, because the last A1 contains a relative row reference.

In addition, although in this case conditional formatting formulae are volatile anyway, in other cases where possible a ROWS(..) construct might be preferred as I understand that ROW() is a volatile function, while ROWS() does not seem to be (Volatile Excel Functions -Decision Models).

 

[markmzz]

Hi Circledchicken,

If the range is the corresponding cells (like the user said), then both formulas work. Anyway, the problem was solved.

Markmzz