colour code one cell by entering data into another cell

**gazza uk** · 2014-05-29, 03:47 AM

I want to make an activity sheet from data entered into different sheets
if you look at division 1&2 season 42 sheet you will see that bazzeriom has played 3 games this season so far , then if you look at the C.T.T.H division 3&4 sheet you will see he has played 2 games but missed 1.Then if you look at the activity sheet you will see that his games are recorded as green cells how would i go about making this activity sheet fill its self in automatically when i input data to any other sheet.Any help would be a starting point

**jsuarezg** · 2014-05-30, 03:50 AM

You make a link between the activity sheet and each game of the diferent sheets. For example in the cell B3 in the Activity Sheet put ='Division 1&2 season 42'!C11 then with conditional format you can make a rule that format the cells only with a fill color and the text color the same, so you can see only a color in the cell.

**gazza uk** · 2014-05-30, 02:20 PM

linking the cells will not work because the cells on most of the sheets move as they are leagues and are updated every game so cell C11 may become cell C12 after I update that sheet. The only sheet that does not move is the activity sheet

**vinod** · 2014-05-30, 11:35 PM

Hello Gazza - Here you go for the solution.

before the solution you need to make few things as standard :
1. Name should be same across the sheet ( Eg: Bazzerion in one sheet, whr as in Active sheet I can see it as Bazzerion(TL)) - easy to do lookup
2. Have a standard row in the activity sheet( see Row 1, which represents Game 1, Game 2 and so on.. again these should be standard across sheets)

Here you go for the solution :
I have used vlookup and match formula to get the value in the particular cell irrespective of any row in the identified name column
I have used 2 Conditional formatting :
a. identify the cell which has >0 as Green cell fill color as 'Green' and font as Green ( to make content invisible)
b. Identify cells which = 0 as No color empty with cell fill color as 'no color' and font as ' white' (to make content invisible)

Attached the file for your reference.

Hope this makes your life easy : -)

Let me know if you have any questions.

Regards
Vinod.