Copying down an indirect formula

**sable** · 08-20-2009, 10:01 AM

How can I copy down an indirect formula? When I copy it the lookup reference doesn't change.

My formula is:
=IF(INDIRECT("Q1")="",INDIRECT("R1"),INDIRECT("Q1"))

but when I copy down the cell reference stays the same (I need to keep the indirect formula because I'm adding columns in column Q but it needs to reference column Q even when columns are added). From reading through some other posts I believe I need to add a ROW() or COLUMN() formula in there somewhere.

Anyone run into this before? Any ideas?

**JBeaucaire** · 08-20-2009, 10:04 AM

If you're putting this formula IN row1 somewhere, then this is an equivalent formula that will adjust the numeric portion upward as you copy down.

=IF(INDIRECT("Q" & ROW())="",INDIRECT("R" & ROW()),INDIRECT("Q" & ROW()))

If that formula isn't going IN row1, but you need the first cell it is in to equal "1" in the numeric, then:

=IF(INDIRECT("Q" & ROW(A1))="",INDIRECT("R" & ROW(A1)),INDIRECT("Q" & ROW(A1)))

**DonkeyOte** · 08-20-2009, 10:05 AM

Can we first enquire as to why you're using INDIRECT ?

Is it because you're inserting / removing rows and/or columns ?

I would probably advocate use of INDEX first off...

**sable** · 08-20-2009, 10:12 AM

Whew! Thanks so much JBeaucaire

This:
=IF(INDIRECT("Q" & ROW())="",INDIRECT("R" & ROW()),INDIRECT("Q" & ROW()))
worked for me...

DonkeyOte, as I said in my original post it's because I'm adding columns that I needed to use indirect. I'm not sure how INDEX works though so that may have been another option. Thanks for your reply!

**DonkeyOte** · 08-20-2009, 10:16 AM

If you're adding colums

=IF(INDEX($1:$65536,ROWS(A$1:A1),17)="",INDEX($1:$65536,ROWS(A$1:A1),18),INDEX($1:$65536,ROWS(A$1:A1),17))

above would return contents of Q1, when copied down Q2 etc etc...

The reason I mentioned INDEX is that INDIRECT is Volatile and brings it with potentially unnecessary calculation overheads. Unless you're deleting entire sheets the INDEX approach is normally viable (& preferable IMO).