Dragging vertical column cells horizontally without changing the letter

**mfs3** · 08-21-2012, 12:17 PM

i know this is possible through the indirect function but im not too savvy with this function.
trying to take for example the value in cell A1 in sheet 1 and hard link to another tab in the same sheet = cell A1. Then drag this hard linked cell across horizontally (trying to pick up the vertical chain i.e. A1, A2, A3 ect. but the letter changes when I drag to A1, B2, C3 ect.
Is there an easy way to lock the row?

**martindwilson** · 08-21-2012, 12:28 PM

try
=INDEX(Sheet2!$A:$A,COLUMN(A1))

**mfs3** · 08-21-2012, 12:53 PM

not really following.... see the attached spreadsheet. in sheet 2 i hardlinked 1 cell and dragged horizontally trying to capture the verical values in sheet 1

**martindwilson** · 08-21-2012, 01:17 PM

=INDEX(Sheet1!$A:$A,COLUMN(D1))

**mfs3** · 08-21-2012, 02:37 PM

awesome!! this works.. many thanks. last question can you combine arrays if the data is in more than 1 row?

**martindwilson** · 08-21-2012, 03:36 PM

for 2 columns of numbers starting a3 and b3
=IF(COLUMN(D1)-4<COUNT(Sheet1!$A$3:$A$500),INDEX(Sheet1!$A:$A,COLUMN(D1)),INDEX(Sheet1!$B:$B,COLUMN(D1)-COUNT(Sheet1!$A$3:$A$500)))