Hi all,
The following formula used in Conditional Formatting works fine for excel 2010 but not excel 2007. Any idea on how to modify it for it to work in 2007??
Formula:
Please Login or Register to view this content.
The formula essentially check whether each cell in column 5 is holiday or not based on a list in column D of sheet 'Details' (both sets of cells are date formatted).
Thanks!
Geoff.
hi geoff. in older versions before 2010, you cannot use a formula directly in CF to reference to another worksheet. a workaround is to name the range. you can also shorten your formula
1. go to Details sheet & select D10:D25.
2. you should see a name box below the ribbon & on the left of the formula bar. it shows the cell reference. click on that & type a name. i'll type Reference. Press ENTER
3. go to the worksheet you wish to apply CF & select from the start range say A1:A10. go to Home -> Conditional Formatting -> New Rule -> Use a formula to determine which cells to format -> Format values where this formula is true:
=COUNTIF(Reference,D$5)
4. format to the colour you desire & click ok
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
=SUMPRODUCT(--(TestBlock=D5))>0,
Named range TestBlock --> Details!$D$10:$D$25
Ben Van Johnson
Thank both!
..... "=COUNTIF(Reference,D$5)" - clever, I knew there was a very simple way to do it!
After my post, I workout the following:
Formula:Please Login or Register to view this content.
Both your solutions and my alternative formula do the trick on my 2010 excel, I have sent the file to the 2007 user but you both seem certain that it should work so I am not worried.
Thanks again.
Cheers,
Geoff.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks