Conditional Formating for Hostel Needs

[Harters]

Hi

I now have an issue I am trying to solve where a cell in sheet 2 (any cell) will either carry or not carry a value which exists in a list in sheet 1.

I would like to colour this cell in sheet2 based on a value in sheet1.

below is what I am trying to acheive....


So if a cell on sheet 2 has the word Apple Street, the formating for that cell needs to look at sheet2, do a kind of vlookup to find the relevant word (Water in 1st example) and then format the color based on that. If the Apple street hostel then later needs more soup to be delivered, we will change Water for Soup on sheet1 and this should change the colour of the cell on sheet 2 with the word Apple Street in it to a different colour.

There are only 5 items that could be listed in the "Need" column

is this possible ???

Sheet1

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Hostel
[/TD]
[TD]Need Column
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apple Street
[/TD]
[TD]Water
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Orange Street
[/TD]
[TD]Clothes
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]High Street
[/TD]
[TD]Bread
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sheet2

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Apple Street
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Orange Street
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]High Street
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Changed sheet1

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ObHostel
[/TD]
[TD]Need Column
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apple Street
[/TD]
[TD]Soup
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Orange Street
[/TD]
[TD]Clothes
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]main Street
[/TD]
[TD]Bread
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Changed sheet2 - now Apple Street is now shown in RED
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Apple Street
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Orange Street
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Main Street
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Sparkle99]

A suggestion...

Next to each hostel column have a hidden column that contains a VLOOKUP for the relevant hostel
A-"Apple Street" B-Vlookup for Apple Street need C-"Orange Street" D-Vlookup for Orange Street need...
Then the Conditional formatting becomes easier and can be copied

For column A, you can have a conditional format that looks at column B and then five rules to set the font colour dependant upon the value in B

 

[Harters]

Sorry I am being thick today

I will explain a little more...

Sheet 2: The hostel name (Apple Street, Orange street...) can exist only once in any cell between A1 and SZ1025 - and we don't know where it will be but only once for sure

The colour fill of the cell whereever the name appears needs to be based on the hostel need table on sheet 1 which is basically 2 colomns... Hostel Name and Hostel Need

There are around 100 hostels and 5 possible values that the adminstrator will assign to a hostel - only one entry will be selected at any given time for each hostel

I tried but can't quite understand what you mean with the hidden column - assume you mean on sheet1 ? we can't play with sheet 2 at all - other than to copy conditional formatting across the entire sheet.

Of course if we were entering the hostel need onto sheet 2 then a 5 rule would be easy... if value = "soup" fill cell GREEN etcbut we need to look up the current need for that hostel where ever it is and colour the hostel name accordingly.

Sorry if I am missing an easy point

 

[Harters]

Anyone though how I can do this with a fomula in conditional formating?

Been trying things like this where the cell on sheet2 is E6

="vlookup($E$6,Summary!$A:$C,3,0),Food"
and also
="vlookup($E$6,Summary!$A:$C,3,0)='Food'"
and also
="vlookup($E$6,Summary!$A:$C,3,0)=Food"
and also
="vlookup($E$6,Summary!$A:$C,3,0)=""Food"""

nothing working

performing a normal vlookup in cell E7 (for example) like =vlookup(E6,Summary!$A:$C,3,0) returns the value Food so I can't see why cond format can't look at the value in its own cell, do a vlookup and if the value returned is Food, fill the cell Yellow.
Then I can have 5 cond formats for the 5 hostel needs but i just can't find the right syntax