• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Offset Formula help

Hi All,

I have to transpose data from rows to columns. I have used offset formula to transpose data in column C to rows starting in column E (please see highlighted in blue). Can you help me with two issues:

1. How to change the formula so I drag it to E2-G2 (highlighted yellow) to get the data in column D7-D9
2. I have this fixed format to transpose the data. I want E3-G3 to pick up data from C24-C26 (highlighted in green). There is 14 rows in between (fixed number of rows between two sets of data)



Would you be happy with a single array formula?
= INDEX(UnitDetails, row, column)
The names 'row' and 'column' are defined to be
= 17*QUOTIENT(k-1,2)+7 + {0,1,2}
= 3+MOD(k-1,2)
Many thanks. I have a bigger range than I posted in the example. I need to figure out to tailor it for the formula to work.

Peter Bartholomew

Well-Known Member
I use array formulas for simplicity but it should be possible to turn the formula into single row formulas copied down.
MS365: replace k by @k, drag down
Traditional Excel: Ctrl/Enter to overwrite the CSE formula; select the top row and recommit with CSE; drag row down.