Hi,

The simplest solution would be to highlight your 'complaint index' range, select copy, then move to your other sheet, select the first cell of your destination column, and use "Paste Special..." with "Transpose".

If you need a formula, please identify the size of the source range and the first cell of your destination column.

Cheers,

Thanks for your reply.

The problem with the copy paste solution is that I don't have all the values yet (they will get updated every day so the formula solution would be best).

I'm not exactly sure what you mean. If you mean the range as in size then it will be between 1 and 10. The first cell of the destination column is "E9" if that's what you mean.

Cheers

Insert this formula in cell E9 of your destination sheet:

Then autofill down.

Cheers,

It seems like an elegant solution but I can't get it to work.

I get an "Invalied cell reference error".

Is there any chance you can explain what each part of the formula means?

I can get this to work for a single field but autofill doesn't work...

Thanks for your help.

This INDEX function will return any value from a range where the row and column within the range is identified.

The first portion:

refers to the source range. As per your description, it is one row deep and ten columns wide. Note that the dollar signs (absolute reference) must remain in place to prevent the formula from losing it's focus while you auto-fill.

The second portion:

identifies which row of the source range to return.

The third/final portion:

does all the work here. It identifies which column of the source range to return. You indicated the destination cell would begin in cell E9, so the ROW() will read that the formula is on the ninth row then subtract 8 from it. As you auto-fill this formula down from the ninth row on the destination sheet, the ROW()-8 will increment 1,2,3,... and provide the formula with a column number. Remember, you're trying to transpose the rows & columns, that's why a row is acting as a column reference.

Hope this helps,

Cheers,

I registered just so I could say that this formulaic discovery was AMAZING! Thanks for sharing it!