Excel Absolute Reference? [Solved/Closed]

Report
-
 gr -
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 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
760
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?
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
760
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")