CODEWITHSUNDEEP
vbaexcel
beeba
beeba

Reputation: 432

Formulas to Switch Row and Column References in Excel

I have raw data that is arranged like this:

enter image description here

The top row is the trial number. Then the first entry in each row is an ID.

I'm trying to set up a template for this data. When this data is pasted in, I want it to be read by another sheet. This sheet will automatically transpose the data, so that it looks like this:

enter image description here

On this sheet, then, I've been trying to write a formula that will increment horizontally when dragged down vertically. When I copy the formula horizontally, I would need it to increase the row reference, not the column reference, so that it'll reproduce the end result in the screenshot above.

I've tried variations on a formula like

INDEX('Asset Returns'!$B$2:$Z$2,COLUMN()-1,ROW()-1)

but I haven't been able to get it working as described. Thanks in advance for any suggestions on what I'm doing wrong.

Upvotes: 0

Views: 162

Answers (1)

Steve Lovell
Steve Lovell

Reputation: 2564

Not sure if I've understood, but if what you're doing is transposition, but wanting to do it via a common forumla in all the destination cells, you can do it using this:

=INDIRECT(ADDRESS(COLUMN(A1),ROW(A1),1,1,"Asset Returns"))

This should be pasted into the A1 cell of your "transposed" sheet, or adjust the cell reference accordingly if that isn't where the data is.

Another option is:

=OFFSET('Asset Returns'!$A$1,COLUMN(A1)-1,ROW(A1)-1)

Upvotes: 1

Related Questions