Change cell value based on the color of another cell

[Woodpusher147]

Hello
I hope someone can help with a (in my not super excel mind) very complicated issue.

I have the above spreadsheet to track performance which has 11 columns and however many staff there are as rows.
The sheet has conditional formating to shade each cell based on score, that's all done ok.
I also have data validation as Bedroom does not score in columns 4,5,6,7,8 - Upstairs & Downstairs do not score in 9 or 10

However, I now need to have an overall score by using the following Key
RED =0
AMBER=1
GREEN=3
YELLOW=5
The other complication is that columns 1,2,3 and 4 will score 4 times more than all other columns but I guess I can do this by having the formula/code populate hidden cell which I can then simply refer to this x4 for the cells in rows 1,2,3,4

Ive looked at an old thread which is titled "Changing cell value based on the color of another cell" and has an interesting
User Defined Function in VBA by Joe4. This could work as I dont want the code to run on all the sheet but I really need help

Rick Rothstein and sheetspread also commented and helped


I hope you can help as my head is now hurting


Thank you for any replies

Simon

 

[cmowla]

What you could do is simply have all of them in one sheet, where you have an extra column which is for manually inputting from which department the rankings are for. That way you can use Excel's data filters to sort by department name and then sort the column with employees names by employee names.

But if the ranking system (and/or conditional formatting colors) is potentially (if it isn't already) different for each department sheet (and/or you want VBA code to do all of what I just mentioned + more, automatically for you), then you probably want to have a separate (master) sheet from which you just pull in the rankings from each of the department sheets.

If this is the case, I believe it would be best if you would make a new thread for this new request. Because this new question is an entirely new one, and it will bother no one if you do. (In fact, since your original question in this topic has already been resolved, I believe it is appropriate to do so . . . so that you can get a lot more people to try to give you the best solution possible. I have done something like this before in the past, but for personal reasons, I am probably not going to post on this forum anymore, as there is a conflict of interest between me and the admin regarding me believing in posting videos in my posts) All you have to do is show them the layout of all of your sheets (with spreadsheet captures from XL2BB) and state the objective you just stated here.

For example, state:

• The master sheet's name, as well as the specific column/cells you wish to pull of the "search results" into. (The destination)
• Which cells those search results need to come from (and their specific sheet names). (The source)
• How you want it to look (just manually type up a portion of the master sheet to give them how you want the end result to be . . . without there being any room for doubt).

And (if this is the case, of course), to help you title the thread something that brings in the right people to answer the question, I would make it something along the lines of "Pull in data from department sheets into a master sheet".

I highly doubt there is a need to mention the conditional formatting (since you don't want to merge the source department sheets into a single source department sheet which also functions as your destination/master sheet.) They only need to know which cell that the formula (and therefore the total scores) is in, not the formula itself.

 

[Woodpusher147]

What you could do is simply have all of them in one sheet, where you have an extra column which is for manually inputting from which department the rankings are for. That way you can use Excel's data filters to sort by department name and then sort the column with employees names by employee names.

But if the ranking system (and/or conditional formatting colors) is potentially (if it isn't already) different for each department sheet (and/or you want VBA code to do all of what I just mentioned + more, automatically for you), then you probably want to have a separate (master) sheet from which you just pull in the rankings from each of the department sheets.

If this is the case, I believe it would be best if you would make a new thread for this new request. Because this new question is an entirely new one, and it will bother no one if you do. (In fact, since your original question in this topic has already been resolved, I believe it is appropriate to do so . . . so that you can get a lot more people to try to give you the best solution possible. I have done something like this before in the past, but for personal reasons, I am probably not going to post on this forum anymore, as there is a conflict of interest between me and the admin regarding me believing in posting videos in my posts) All you have to do is show them the layout of all of your sheets (with spreadsheet captures from XL2BB) and state the objective you just stated here.

For example, state:

• The master sheet's name, as well as the specific column/cells you wish to pull of the "search results" into. (The destination)
• Which cells those search results need to come from (and their specific sheet names). (The source)
• How you want it to look (just manually type up a portion of the master sheet to give them how you want the end result to be . . . without there being any room for doubt).

And (if this is the case, of course), to help you title the thread something that brings in the right people to answer the question, I would make it something along the lines of "Pull in data from department sheets into a master sheet".

I highly doubt there is a need to mention the conditional formatting (since you don't want to merge the source department sheets into a single source department sheet which also functions as your destination/master sheet.) They only need to know which cell that the formula (and therefore the total scores) is in, not the formula itself.

Sad you wont post here anymore and that your videos were not to someone's liking. It really helped me

Thank you very much for your help

Take care

Simon