Conditional formatting "kept constant"

  • Thread starter Thread starter Morten
  • Start date Start date
M

Morten

I have the following structure in a normal worksheet:



A 18

B 12

C 15



where the letters in column A are formatted (in this case by colour)
depending on the

numbers in column B, say, for instance, the letter is red if the number in
the same row

is less than 14. In this case, letter B would be red whereas A and C are not
coloured.

No problem in this.



Now I need to copy column A to another worksheet, leaving out the numbers
but

keeping the "colour code" per letter (ie. keeping the red colour of letter
B). I have tried

to Copy->Paste Special but this won't, probably naturally enough, work.



Ant ideas ?



Thanks a lot in advance,



Morten
 
If I write my conditional formatting like:
=B1>3
Then when I copy and paste to a new sheet, it points at B1 of the new sheet.

If I try this (for A1 on sheet1):
=sheet1!b1>3
xl yells at me and says I can't use references from another worksheet or
workbook.

But if I cheat and use this:
=indirect("sheet1!b1")>3
then it works on sheet1.

And when I copy and paste to a worksheet, it still points at sheet1, b1.

Be aware that:
=indirect("sheet1!b1") will always point to B1 of sheet1--even if I add/delete
rows or columns. So it might not work if you're adding/deleting stuff later.
 
Back
Top