I am trying to create a ranking structure but it does not seem to work. I want cell A4 to highlight green, or red depending if the % value is higher then Cell D4 on another sheet which is the target. But its driving me nuts... sadly I will admit I have not used excel since high school.
Conditional formatting will not cross worksheets unless you get into named ranges or VBA.
Could you link D4 to the ranking sheet and then we could use that cell as the cell to create the condition from or do you want to go with a named range or VBA?
Are you just looking at the one cell D4 or in the end, many different cell on the other sheet?
HTH
Regards, Jeff
The CF formula on cell A4 would be something like this for a 25% threshhold:
=ABS(Sheet2!$D$4-A4)/Sheet2!$D$4>0.25
On Excel 2003 and earlier you would need to NAME the cell D4 on sheet2. If define the name as MyVal then this CF formula would work:
=ABS(MyVal-A4)/MyVal>0.25
Last edited by JBeaucaire; 05-18-2012 at 08:09 PM.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
many different cells, The sheets in the work books are as follows
Summary, Targets, Week 1, Week 2, Week 3, Week 4. Essentially the TARGETS will be the quarterly goals to store the data, and then on SUMMARY and WEEK sheets if those targets are not being met it would highlight the cell of the corresponding target for easy reference.
So if the goal was 8% but on WEEK 1, or SUMMARY the total for that product line was only 5% it would highlight red, but if it was at or over 8% it would highlight green.
Is there a way you can link the target to the summary sheet on then base your conditional formatting there off of the goal?
I tried Jerry's initial suggestion, but for me, I can't get Excel to recognize a cell from another sheet unless it is a named range. I must be missing something!
Hmmm, I suppose I could put a "TARGETS' Cells on each sheet, that pulls from the main "TARGETS" Sheet and then when I change main targets I would auto adjust all other fields in all sheet. Hmmm ill have to try that or a version of it.
Yeah that works, but that is going to be a pain and screw up some of my other sheets. Any other ideas on how to get around that?
Hard to say without seeing what you are dealing with, how many cells and so forth!
Hi Jerry,Originally Posted by JBeaucaire
So what's the secret?
I'm using 2007 and after entering this formula in CF I get "You cannot use references to other worksheets or workbooks for Confitional Formatting criteria"
Is it possible this is only for 2010 and not anything lower?
You're right, Jeff. It was introduced in 2010 version.
2003 and 2007 and 2010 should all be able to use the second version of that formula that uses a named cell on the remote sheet.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks