# Excel Absolute Reference?

Solved/Closed
gr - Aug 10, 2010 at 02:44 PM
gr - Aug 11, 2010 at 11:18 AM
Hello,

I have cells A1:A5 as "5, 10, 15, 20, 25" respectively.

I want to make cells C1:G1, reflect cells A1:A5. Therefore, C1=A1, D1=A2, etc...

I tried to do the autofill by dragging on the lower right hand corner of Cell C1 and D1, while putting an absolute reference, =\$A1, =\$A2.

However, it won't autofill. It keeps replicating only =\$A1, =\$A2, instead of going down the series, i.e., A3, A4, A5.

Is it because the first data range is vertical (all in one column) and I'm trying to drag/autofill in a horizontal range (all in one row?)

Thanks.

## 3 responses

rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Aug 11, 2010 at 07:23 AM
Yes it is because that when you drag down, it means row needs to be changed (default behaviour) but when you drag across columns, then it means columns would change.

You have to use a combination of formula to do
=INDIRECT("A" & COLUMN()-2)
column() function gives the index of the column where the formula is written, then it subtract 2 from that number. So if you write this in C1, you would get

column() = 3
column()-2 = 1

then indirect basically just uses "A" & 1 = A1 cell address to give you the value
Thanks for the formula combination.

What if I were to have the values on different worksheets?

For example, cells A1:A5 (5,10,15,20,25) would be on Sheet 1.

On Sheet 2, I want to input the formula combination for cells C1:G1.

How could that work, is it possible?
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Aug 11, 2010 at 10:33 AM
Indirect should still work. In this case you string the sheet name too as was done for column

=INDIRECT("Sheet1!A" & COLUMN()-2)

If you look at the help for indirect, you would see all it does is instead of

usual
=A1

it takes an string and then transform that to address.

=INDIRECT("A1")
Thanks!