Copy non absolute cell references in conditional formating formula

**AndyGW** · 12-03-2012, 11:15 AM

Hi

I have this fairly simple formula which decides whether to shade a cell or not

=AND($X$1<>"TBD",R3<>"None",AC3="Y")

This is set in cell R3 and I want to copy it all the way down the cells in the R column. However, when I copy & paste (and copy and paste using paste special, formatting) the R3 and AC3 cell references do not update to match their relevant rows. eg If I highlight cell R26 the conditonal formatting formula still refers to cell R3 and AC3, not R26 & AC26. I'm using Excel 2010 but I don't recall this happening in 2003. Is there soemthing I'm missing, an option setting for example?

**rcm** · 12-03-2012, 11:22 AM

how about using and indirect reference?
Formatting copying only copies the attributes of the cells not the formulas, try copying fully or try copying the formulas...

If everything fails the new formula could look something like this

=AND($x$1<>"TBD",indirect("worksheetname!r"&row(a3))<>"None",indirect("worksheetname!ac"&row(a3))="Y")

substitute worksheetname with the name of your worksheet, I started at row 3 because you had it there.

regards,

RCM

**AndyGW** · 12-03-2012, 11:33 AM

When you say "copy fully" I take it your mean a simple CTRL C & CTRL P. That makes no difference, the conditional formatiing fomulas do not update. I'll try the formula you suggest instead.

**rcm** · 12-03-2012, 11:37 AM

Dear AndyGW

I try copying your formula (I copy/pasted it) and it changed the reference rows...

**AndyGW** · 12-03-2012, 11:49 AM

Then I have no idea what is going wrong my end. I just tried again and the cells refs do not update. I've uploaded my workbook. Ive stripped out all the data so the cells themselves all report in error but the formatting is still correct. I just tried again and no matter how I copy& paste the cells I paste do not update their conditional formatting refs.

Thanks for your help.

**AndyGW** · 12-03-2012, 11:54 AM

As a test I just set up a super simple new workbook and put the conditional formatting formula =AND(a1=1) in the cell A1, then copied it down the A column... none of the cell refs updated beyond saying A2 all the way down. If its a local problem then somehow my excel is porked.

**AndyGW** · 12-03-2012, 12:00 PM

Sorry yet more info. I just rechecked that sheet I made an even tho the cells refs don't update the code is working. ie If I set any of the cells to something other than 1 the shading turns off. Am I just reading it wrong? I've just come from xl 2003. This is what I do - I highlight the cell, click the conditional formatting tab which opens up the cond formating dialogue box, then click "manage rules". The rule shows the formula and the cell range it applies to. The range is right but if I view the formula itself, no matter what cell I highlight the formula always reads as A1 or A2.

**rcm** · 12-03-2012, 12:01 PM

You lost me there with your uploaded file... the only formula I could find is a Vlookup with and invalid reference

**AndyGW** · 12-03-2012, 12:05 PM

Yes it the conditional formatting formula on the cells Im having a problem with, not the cell formula itself. Check any of the "Raid" column cells and open up the conditional formatting dialogue box.

**rcm** · 12-03-2012, 12:34 PM

Andy:

I reread your first post. If you set the formula posted there in r3, you get a CIRCULAR REFERENCE error.. in which column are you setting the formula?

**AndyGW** · 12-03-2012, 12:42 PM

I dont believe this to be a problem as this is conditional formatting. All conditional formatting is essentially a circuclar reference, eg if cell value = 1 turn red. The format refers to the cell that it is going to change.

**AndyGW** · 12-03-2012, 01:05 PM

I've created an example workbook with notes highlighting exactly what the problem is. This may just be an unavoidable glitch but if so, it's annoying as hell.