Change cell value based on the color of another cell

Woodpusher147

New Member
Joined
Oct 6, 2021
Messages
39
Office Version
  1. 365
Platform
  1. Windows
Hello
I hope someone can help with a (in my not super excel mind) very complicated issue.
1633520453103.png


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
1633519851369.png
 

cmowla

Board Regular
Joined
Sep 21, 2021
Messages
243
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Woodpusher147

New Member
Joined
Oct 6, 2021
Messages
39
Office Version
  1. 365
Platform
  1. Windows
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,783
Messages
5,766,440
Members
425,355
Latest member
newox1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top