Transfer shading to different sheets

[daisy8]

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 :smiling_face_with_smiling_eyes:

Many thanks 

[#2]

@daisy8 

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.

[#3]

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

[#4]

@daisy8 

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.

[#5]

@mathetes , file is here

[#6]

@mathetes 

It is a dummy workbook with fictional names.

In fact, I simplified the workbook right down so that only the two queries I wanted solved were shown.

I don't know how too much traffic could be an issue as it is only yourself I have had a response from.

However, another person has managed to share the link to the dummy workbook so hopefully you should be able to access it through him.

[#7]

@daisy8 

I haven't done the colorizing yet, but wanted to show you this.

You will need a relatively new version of Excel, as this makes use of the FILTER  and LET functions.

As you'll see, it's not necessary to have separate sheets for each individual. Assuming you're just going to print one for Bluey, another for Bingo, another for Chili, etc, you can simply select the name on one of the two sample sheets I've given you (the one titled "Version2" is the cleaner) and it does the work of getting that person's data.

[#8 · Accepted solution]

@daisy8 

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]

Thank you for the time you have taken to answer my query.

[#10]

@daisy8 

It's been fun. I enjoy learning as I do things like this--your situation presented some challenges to me at my level of Excel understanding. I trust it's working as you want. If not, come back with further questions.