Results 1 to 9 of 9

Thread: colour code one cell by entering data into another cell

  1. #1

    colour code one cell by entering data into another cell

    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Attached Files Attached Files

  2. #2
    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.
    Microsoft Excel Massive Open Online Course.
    Learn all about Microsoft excel in this Online course, with video tutorials, examples of applications and a complete functions and buttons explanation.
    Be part of our community and enjoy learning Excel.

  3. #3
    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
    Last edited by gazza uk; 2014-05-30 at 02:24 PM.

  4. #4
    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.


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

  6. #6

    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
    Attached Files Attached Files

  7. #7
    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

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

  9. #9
    You are welcome!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts