Forum Discussion
Solved
Transfer shading to different sheets
Hi,
I am creating a spreadsheet to record badge achievements for a club.
I would like to create one sheet to show overall achievements and individual sheets for each member.
I would like to show the achievements by shading the cells: my question is can shading be transferred between sheets in the same way as data?
If not, any suggestions are welcome 😊
Many thanks
You can ignore that last posting. Here's a greatly improved version (still retaining versions 1 and 2 if you're curious).
In the tab called "version 3" I have added the color when there's a value in the cell under award or time. This also uses a slightly simpler formula, although still retaining the LET function to make it a bit shorter.
9 Replies
my question is can shading be transferred between sheets in the same way as data?
To that question, I'm pretty sure the answer is "no, not transferred the same way."
But of course, there are multiple ways to transfer data between sheets and multiple ways to accomplish shading, so maybe we should back up a bit and talk about the whole design.
What kind of rule/heuristic/ guideline will determine what kind of shade gets applied? Are you expecting a different color for each kind of achievement? A different color for the number of achievements regardless of the type? Something else?
It's good that you're considering some kind of sheet with overall achievements and (from the sound of it) hoping to extract the data for each individual from that more comprehensive database. That can be done with the use of the FILTER function, and that could be embedded in a SORT function if listing individual achievements in some particular order is desired.
Assuming that the shading has to do with the type of award, you could use conditional formatting so that all awards of type A are colored one way, type B another, and so on. Then the same conditional formatting rules could be used in the sheet used to extract the individual records. But you'd need to enter the rules in each sheet.
Do you have a copy of your current workbook that can be shared (populate it with fake names, maybe Disney characters) by posting it on OneDrive or GoogleDrive with a link pasted here that grants access. That way one of us here could demo using your layout.
Hi, mathetes
Thank you for your reply.
I have attached a sample workbook.
The password is Tif2nrpr#
A brief explanation of the system I am using; the achievements are grouped into themes which have a different colour. Participants are required to earn a level award and attend a certain number of sessions in order to achieve the award for that theme.
The cells beneath the subheadings would need to be colour-coordinated for ease of use. Once the achievement has been gained, the shading would be changed to the corresponding colour and remain that colour to show it had been completed.
As you mentioned in your reply, there would then be several sheets following the first, one for each individual. These will need to contain the same information as the first for each individual. I will also need to transfer some number data (just numbers, no formulas) to the individual sheets to show the minutes each individual had attended.
I hope that is sufficient information for you but please do ask if you need more detail.
Thanks again for your help.
My knowledge of Excel is very basic but I'm keen to learn 🙂
Daisy
I'm not sure exactly why this is happening, though I have a hypothesis. When I clicked on your link and entered the password you provided this screen showed up
My hypothesis is that too many folks here in this forum have tried to enter it---it may take only a few--and that triggers this locking response. So I'd strongly suggest you get yourself out of trouble by removing the link (in fact I'll notify the administrators of this site to do so, if you don't get there first).
If the sheet you're linking has real names, create a dummy version of it without real names and then share it without requiring a password.
