CODEWITHSUNDEEP
excel
Aaron R
Aaron R

Reputation: 3

Excel: Increment column letter

I have an excel workbook with two worksheets. On one sheet I have data horizontal and my second sheet I have data vertical. I want on sheet 2 to drag down my reference from sheet 1 data. I am looking to have column increase not my row increase? Makes sense? I try to "equal" the cell i want from sheet 1. However when I drag down my reference goes down instead of across. Any help would be appreciated.

Sheet1

Sheet2

Upvotes: 0

Views: 3757

Answers (1)

teylyn
teylyn

Reputation: 35915

Conceptually: Use Index. The syntax is Index(range,row number, column number)

If you want to transpose a range in Sheet1 to Sheet2, you can use

=Index(Sheet1!$A$1:$J$10,column(A1),row(A1))

Copy across and down.

See how the row argument uses the column command and the column argument uses the row command. That means in the starting cell A1, the INDEX formula will return the column number 1 for the row argument and the row 1 for the column argument, i.e. 

=Index(Sheet1!$A$1:$J$10,1,1)

Copy the formula to row 2 and the reference in the column argument will refer to Row(A2), so the formula will translate to 

=Index(Sheet1!$A$1:$J$10,1,2)

i.e. it will return the value from the second COLUMN of the index range. See the screenshot for a result. Change the Index range to your desired source.

enter image description here

Upvotes: 1

Related Questions