VLOOKUP & Conditional Formatting

[amish_z]

Hi

New to this forum but it's always been helpful in the past so thought i'd give it a go myself!

I have created a drop-down list for a Dashboard which then uses VLOOKUP to populate the next 6 columns with the data. This data is for a set period e.g. (this month)

On the next sheet, I have the same thing but the data is for a previous period (last month)

I want conditional formatting to look at the data in the Dashboard and change color according to the data for the previous period e.g.

I select "A" from the drop down and vlookup returns £100 for last month, the previous month it was £50 so make the cell go GREEN, or if last month was £150, the cell goes RED

Please help!

Thanks

 

[AOB]

If the cells to be compared are in the same location on both sheets (i.e. compare B2 on one sheet with B2 on the other sheet) then you could use the INDIRECT function in the condition formatting formula?

Along the lines of :

=$B1>INDIRECT("LastMonth!"&ADDRESS(ROW($B1),COLUMN($B1)))

and :

=$B1<=INDIRECT("LastMonth!"&ADDRESS(ROW($B1),COLUMN($B1)))

<indirect("lastmonth!"&address(row($b1),column($b1)))[ CODE]
<indirect("lastmonth!"&address(row($b1),column($b1)))[ CODE]

with appropriate formatting applied to each?

(I've used LastMonth as the name of the sheet to compare against; you will need to edit accordingly)</indirect("lastmonth!"&address(row($b1),column($b1)))[></indirect("lastmonth!"&address(row($b1),column($b1)))[>

 

[amish_z]

That's great


I'll check if the cells match across sheets, if so, happy days!

 

[AOB]

If they don't, a more robust formula would probably be some kind of INDEX / MATCH or COUNTIFS / SUMIFS / SUMPRODUCT but couldn't assist with that without knowing more about the layout and structure of the sheets