Transpose cell locations to another sheet

[tghcogo]

Hi

Using version2013

I'm trying to transpose data in a column to a row on another sheet.
The data is updated several times daily.

I have in the column the following formula =IFERROR(RANK(B4,$B4:$O4,1),"")

When I Transpose it to another sheet I get =IFERROR(RANK(#REF!,$B4:$O4,1),"")

I can manually transpose the values but I want it to update dynamically.

It works if I manually enter =Sheet2!$R4 and then =Sheet2!$R5 etc. for each individual cell, but I have over a thousand cells !!!!!!!

I have tried fill-in but that does not give the progression, I hope you wizards can help otherwise I need to switch my brain off for a couple of hours and do it manually!

[Bernie Deitrick]

You cannot transpose within x rows of the top of the sheet, where x = column with the formula - the column with the leftmost reference +1. So if your formula is in column T, then you should transpose into row 19 or higher: x = 20 - 2 (because you reference B) +1 = 19.

But, if you want to base your calculations on the first sheet, what you should do is use a formula like

=INDEX(Sheet1!$T:$T,COLUMN(A1) + offset)

where offset is the number of the row with the first formula - 1, copied to the right for 1,000 or so cells.