Excel Absolute Reference? Solved/Closed

Question

Hello, 



Configuration: Windows XP / Firefox 3.6.7

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.

rizvisa1 · Answer

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

gr · Answer

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?

gr · Answer

Thanks!

Excel Absolute Reference?

3 responses

Similar discussions

Subscribe To Our Newsletter!