colour code one cell by entering data into another cell

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

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.

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

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.

Sorry was away for a few days could you do one more on a different row say so I can see the difference because I cant understand it 100% yet

not sure wha'ts wrong

OK not sure what i am doing wrong changed name to suit sheet for vlookup but still a mistake what am i missing out ??activity sheet luisoootl Cell B7

Hi Gazza - if you look at your formula Match looking at B4 instead of B1 - the same I mentioned in my explanation above for the #2

ok got it thanks a lot slowly this sheet is taking shape but still loads to work out thanks for your help

You are welcome!