How do I phrase the conditional formatting formula if I want it to highlight a cell on the sheet 'Boards' based on the contents of a cell on the sheet 'Wk1'?
In the sample spreadsheet, on the 'boards' tab I would like to highlight a cell in column D only if for the corresponding board registration number on the 'Wk1' tab, column K is "Y". My actual spreadsheet will have 52 tabs to start with and the 'boards' tab will consolidate all the data from the other tabs.
"Laugh? I thought I'd die!"
Jimbo?
For D2:Dx where x is the row of data, apply this rule using a formula:
Formula:
Please Login or Register to view this content.
Thanks for the quick response. I tried this and got an error I've never seen before:
Capture.JPG
That error has been around for a long time - you are not allowed to explicitly reference another worksheet in a CF condition. However, you can usually get round it by making use of a named range. Define the named range data_table to refer to A2:K630 on the Wk1 sheet. Then in the Boards sheet you can select all the cells from D2 to D773 and then click on Conditional Formatting | New Rule | Use a Formula... , then you can use this formula in the dialogue box:
=VLOOKUP(D2,data_table,11,0)="y"
Click on the Format button | Fill tab and choose your colour, then OK twice to exit the dialogue box.
I only found 4 matching records when I applied this to your sample file.
Hope this helps.
Pete
Thank you for your help!
You're welcome - thanks for feeding back.
Pete
Sorry about that. I thought that restriction was removed in 2007, but it must have been later. I have 2013 and 2016, and it works fine in 2016.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks