Look up a conditional format value in a formula

Hi all,

I'm trying to look up a conditional format value in a formula to show the difference between the cell value and the conditional format value. The spread sheet shows number of hours required in a month, the conditional format is set to show text in green if the cell value is less than or equal to the total hours available in the month, and red if it is greater than. The conditional format value of the current month I change weekly when I import the forecast pivot table as there is less time in the month as each week passes.

Here are the values I use depending on how many weeks there are in a month (everything is based on how many Friday's there are in a month just to make life easier)
1 Week = 666.9
2 Weeks = 1333.8
3 Weeks = 2000.7
4 Weeks = 2667.6
5 Weeks = 3334.5

So if I have 3 Friday's left this month my conditional format says if the cell value is less than or equal to 2000.7 then the text is green, if the cell value is greater than 2000.7 then the text is red. The cell value is a simple formula adding all the hours required for that line in that month.

Now what I'd like to do is have a value in the cell below which basically says what the difference is between the actual cell value and what the conditional formats value is.

For example, if the cell value was 2100.7 I would like the cell below (where I'm putting this formula) to look at that the difference between that value and what the conditional format value of that cell is and return that figure, so in this case it would be -100.


Does that make sense?

Thanks in advance.

Hi, and welcome to the forum.

You can't look up a conditional format formula without VBA (that's a hostage to fortune in these hallowed portals!), but why not keep a copy of the CF formula in a cell somewhere, and test/lookup against that?

HTH

Hi Richard,

Thanks for the quick reply.

I have the values on the spread sheet as I always need to refer to them so I can manually do it, was hoping I may be able to automate it somehow as it's quite a manually intensive spread sheet when crossing a month. Never mind I will just have to write a simple formula referencing the correct value for each given month.

Cheers

Cro