0
Undo
Votes
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
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
0
Undo
Votes
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
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
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
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
0
Undo
Votes
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.
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.
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
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.
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.
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
- Page :
- 1
There are no replies made for this post yet.