Note: The other languages of the website are Google-translated. Back to English
Support is Offline
Today is our off day. We are taking some rest and will come back stronger tomorrow
Official support hours
Monday To Friday
From 09:00 To 17:30
  Wednesday, 17 November 2021
  5 Replies
  1.9K 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.