Conditional Formatting with absolute references
 

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

Conditional Formatting with absolute references
 

The Indirect() function should help:

=Isblank(Indirect("f8"))
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"atljar" wrote in message
...
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

Conditional Formatting with absolute references
 

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

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

-jared

Conditional Formatting with absolute references
 

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

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"atljar" wrote in message
...
I have asked SOO many people about this, and no one could help.

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

-jared