Increment Sheets, but keep cell reference the same on drag

**Alostsoul** · 02-28-2013, 03:07 AM

Hi all,

I'm sorry if this question is redundant, but i'm struggling with the references I've seen. I'm trying to drag/copy a cell with the formula = ='1'!$C$12. Ultimately I want '2', '3', etc. I'm aware that I can use =INDIRECT(), but i'm clueless when it comes to putting the appropriate values in. I've tried to replicate formulas I've seen in other threads with no success. Any help will be greatly appreciated.

Best

**:) Sixthsense :)** · 02-28-2013, 03:21 AM

Formula:

Please Login or Register  to view this content.

Drag it down..........

**benishiryo** · 02-28-2013, 03:21 AM

hi there. so do you want to copy your formulas down or to the right? for copying down:
=INDIRECT("'"&ROW(1:1)&"'!$C$12")
to copy to the right:
=INDIRECT("'"&COLUMN(A:A)&"'!$C$12")

**Alostsoul** · 02-28-2013, 03:35 AM

Thank you both so much! Can you explain the process? I'd like to understand why you're putting inputs in certain areas. If not, that's cool too. I appreciate the help!

**:) Sixthsense :)** · 02-28-2013, 03:37 AM

Originally Posted by Alostsoul

why you're putting inputs in certain areas

Not sure what do you mean by the above quoted sentence

**Alostsoul** · 02-28-2013, 03:45 AM

If I wanted to replicate this on my own how would I know what to write. When you used "Row (A1)", why 'A1',etc? I'm trying to figure out what the formula means. Thank you so much!

**:) Sixthsense :)** · 02-28-2013, 04:01 AM

just put it in a separate cell and see

=ROW(1:1)
=Row(A1)

Both will result 1 as result and when you drag it down then the reference will get rotated to 2:2 and a2 like that the increment will happen when it is dragged to next row.

This part "'" we are adding on precaution, if suppose the sheet name consist the space then it needs to be referred with a single quote, so in better practice mentioning the "'" is a safer approach.
INDIRECT("'"&

I believe you are aware of the rest of our approaches in the suggested formula.