Conditional Formatting

A

Adam Ronalds

How can I get the formula in my conditional format to be dynamic? for example
A B C D
1 100
2 120
3 80
4 100
5 150

How can I get the conditional format =((A2-A1)/A1)>A1*.2 to be dynamic so I
can copy the conditional format from cell A2 through to A5 referencing the
cell above it?
 
A

Adam Ronalds

I want to be able to have a formula in a conditional format be dynamic so
that I can copy format to any cell in the workbook and have that cell
recognize the formula as if it were not anchored to a given cell. in the
example I gave I want to be able to copy special format the conditional
formula =((A2-A1)/A1)>A1*.2 make print green to cells A3-A5 where I don't
have to rewrite the formula in each cells condition....is that possible?
 
G

Gord Dibben

Yes...........as long as you make sure Excel does try to "help" you by
placing $ signs in the cell references.

i.e. you don't want $A$2 or A$1 or similar.

As FSt1 points out...........it can be copied anywhere as written.


Gord Dibben MS Excel MVP
 
F

FSt1

hi
as a test, i copied your formula and data and pasted it into a blank
workbook. i entered the formula into CF. i then copied the cell with the CF
and pasted special > formats. all of the cells references adjusted to
reference the cell above. i think that you would be able to copied the
formating to anywhere in the workbook so long as you paste special > formats.
to make the formula static you would have to add absolute references i.e.
=(($A$2-$A$1)/$A$1)>$A$1*.2
writen as above, the cell reference would never change but you have relative
references so the formula should adjust.
try it and see. post back if it doesn't perform as expected(desired). also
post what you did.

Regards
FSt1
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top