Conditional Formatting with absolute references

[Guest]

I am using MS Excel, version 2000.

Column A is filled with numbers, 1-25 in order, but does not corespond to
excel's row numbers. These cells have condition formating on them, based on
data in columns F.

Columns B through K are filled with data.

Here is the problem....

I need to go in and delete a row of information, from column B-K. I
highlight all the data in any row, from column B-K, and then delete. I select
for all rows to move upward. This leaves column A in tact, and moves up all
in B-K, which is what I want.

However, the conditional formula, in row A is automatically changed by excel
to reference where that cell has moved upward. I do NOT want this to change.
I want the original formula entry to be an absolute reference, reguardless of
if that cell gets moved upward by a delete command/shift cells up.


As my conditional format for cell A5, I have...

1.) formula is =ISBLANK($F$5)
2.) formula is =DATEDIF($F$5,H1,"yd")>21
3.) formula is =DATEDIF($F$5,H1,"yd")>14

Where f5 should be absolute, the value will automatically change if cells
are shifted upwards. Lets say I delete the data in rows 3 and 4, from B-K.

The conditional formats will change to
1.) formula is =ISBLANK($F$3)
2.) formula is =DATEDIF($F$3,H1,"yd")>21
3.) formula is =DATEDIF($F$3,H1,"yd")>14


-Thanks for the help,
Jared

 

[RagDyer]

The Indirect() function should help:

=Isblank(Indirect("f8"))

 

[Guest]

I have asked SOO many people about this, and no one could help.

This worked 100%, thank you, thank you, thank you!

-jared

 

[RagDyer]

You're welcome, and thank you for the feed-back.