Copying and pasting INDIRECT formula

[ala4900]

I'm working in Excel 2010 on a sheet that will be compiling data from a different worksheet based on the current month. I'm using the formula:

=IF(INDIRECT($K$7&"!C45")>"",INDIRECT($K$7&"!C45"),"")

to complete the field using the other sheets value if it's not empty, where $K$7 references a worksheet name based on the previous month.

It's working just fine, but I want to copy and paste it into other cells having the referenced cell, C45, change as I move it around the sheet (if I copy and paste it 3 cells to the right it would become =IF(INDIRECT($K$7&"!C48")>"",INDIRECT($K$7&"!C48"),"").

I haven't been able to find a way to make the C45 cell reference dynamic while copying and pasting. It would save me a ton of time if I could.

Any help would be greatly appreciated!

[mellowmarshall]

You'd have to rework the logic, since that's being read as a string.

/thinkingcap

Maybe something like

Please Login or Register  to view this content.

This, of course, is assuming the column containing this code is the same as the column you're pointing at in the other spreadsheet.

[ala4900]

Couldn't get this to work. Yes the column is the same, in fact it's the exact same cell on the other worksheet.

[XOR LX]

Hi,

Edit: "if I copy and paste it 3 cells to the right it would become =IF(INDIRECT($K$7&"!C48")>"",INDIRECT($K$7&"!C48"),"")"

I presume this is an oversight on your part? Surely you mean it to become:

=IF(INDIRECT($K$7&"!F45")>"",INDIRECT($K$7&"!F45"),"")

if pasted 3 cells to the right (and what you have if pasted 3 cells down)?

Try:

=IF(INDIRECT(ADDRESS(ROW(C45),COLUMN(C45),4,1,$K$7))>"",INDIRECT(ADDRESS(ROW(C45),COLUMN(C45),4,1,$K$7)),"")

Regards

[ala4900]

Hi,

Edit: "if I copy and paste it 3 cells to the right it would become =IF(INDIRECT($K$7&"!C48")>"",INDIRECT($K$7&"!C48"),"")"

I presume this is an oversight on your part? Surely you mean it to become:

=IF(INDIRECT($K$7&"!F45")>"",INDIRECT($K$7&"!F45"),"")

if pasted 3 cells to the right (and what you have if pasted 3 cells down)?

Try:

=IF(INDIRECT(ADDRESS(ROW(C45),COLUMN(C45),4,1,$K$7))>"",INDIRECT(ADDRESS(ROW(C45),COLUMN(C45),4,1,$K$7)),"")

Regards

My apologies. Yes, that should have read "If I copy and paste it 3 cells to the right it would become =IF(INDIRECT($K$7&"!F45")>"",INDIRECT($K$7&"!F45"),"")"

I tried =IF(INDIRECT(ADDRESS(ROW(C45),COLUMN(C45),4,1,$K$7))>"",INDIRECT(ADDRESS(ROW(C45),COLUMN(C45),4,1,$K$7)),""), but that's returning #REF!

[XOR LX]

Works for me. See attached.

Post a sample if that doesn't clarify things.

Regards

[daddylonglegs]

Try using CELL function which can convert a reference to a text value, i.e.

=IF(INDIRECT($K$7&"!"&CELL("address",C45))>"",INDIRECT($K$7&"!"&CELL("address",C45)),"")

[ala4900]

OK, this worked:

=IF(INDIRECT($K$7&"!"&SUBSTITUTE(ADDRESS(1,(COLUMN()),4),"1","")&ROW())>"",INDIRECT($K$7&"!"&SUBSTITUTE(ADDRESS(1,(COLUMN()),4),"1","")&ROW()),"")

I'm not sure why. If anyone wants to explain that to me, I'd appreciate it!

Thanks all for your help!

[daddylonglegs]

Did you try my suggestion, it's a little shorter......

[ala4900]

I did, I got a #REF! error.