Note: The other languages of the website are Google-translated. Back to English
Support is Online
We're back! We are here to assist you. Please be patient, we will respond to your tickets shortly.
Official support hours
Monday To Friday
From 09:00 To 17:30
  Wednesday, 17 November 2021
  5 Replies
  1.8K Visits
0
Votes
Undo
Greetings,

I am creating an assignment log for my work and I have Sheet1 acting as a display of who has what equipment assigned to them. This info is updated from other sheets. On these other sheets, I have a conditional formatting drop down list with 5 colors essentially White, Red, Yellow, Green and Cyan for Not Issued, Missing, Out of Service, Issued and Returned respectively.

I have the item that is issued with an ID number such as FL-01 to indicate which item they were issued. I would like to retain that information in its cell but be updated with the corresponding color when I select it from the drop down list.

For confidentiality reasons, I am unable to upload the file but I can say that I have 9 columns from C to L and 29 rows. Each column C through L has its own sheet that corresponds to it.

Another example: I want Column D with FL-01 in Cell D8 to show Green from the Drop Down Cell in C-8 on Sheet2 and change when I change the Drop Down option.

Thanks in advance,

Viepyr
10 months ago
·
#2350
0
Votes
Undo
I guess what I would like to do isn't possible in Excel or I haven't explained it well enough.

Thanks,

Viepyr
10 months ago
·
#2351
0
Votes
Undo
Hi Viepyr,

Can you send us the file (please delete your private information and replace them with aa, bb, ...)? I don't really understand your question.

Amanda
10 months ago
·
#2352
0
Votes
Undo
Thanks for responding, so here goes.

In the file, I have Sheet_1 which is protected and not editable and Sheet_2 which is not protected. On Sheet_2 I have Column E named Status with a Drop Down list containing 5 items. 4 of those are color-coded.

What I am wanting to do, is when I select Issued, the cell turns Green. I would like that color to shade the corresponding cell on Sheet_1 without changing the content of that cell.

I have the sheets set up to where I enter data in C8 it populates the corresponding cell on Sheet_1

I hope that's a little more clear.

Viepyr

  Test File.zip
Attachments (1)
10 months ago
·
#2353
0
Votes
Undo
Hi Viepyr,

Can you unprotect the Sheet 1? If you can, please do as follows:

1. Click on the cell H2 from Sheet 1, then on the Home tab, in the Style group, click Conditional Formatting > New Rule. In the pop-up window, click the last option - Use a formula to determin which cells to format.
  color fill.png

2.  Then click the up arrow to select the cell C2 on the Flashlisht_Holster sheet. Remember to delete $ after selecting to make the cell reference dynamic. Then add ="Missing"Or you can just copy =Flashlight_Holster!C2="Missing" in the box.
color fill 2.png

3. Now click Format to pick the background color. Then click OK.

Please repeat the above 3 steps to create rules for the other four Status.
=Flashlight_Holster!C2="Issued" > green
......


After you finish the rules, you can just drag the fill handle down to apply the rules to the cells below H2.

Tell me if it works :)

Amanda
10 months ago
·
#2354
0
Votes
Undo
Beautiful!

Thank you very much! I wasn't 100 percent sure it was going to be Conditional Formatting or would have to venture into VBA coding.

I do have another issue that I would be creating a post on when I have time but for now this solves my issue.

Again, thanks a lot.

Viepyr
  • Page :
  • 1
There are no replies made for this post yet.