Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

I can tell you "how to do it", and add that the solution comes with disadvantages. Any solution by formulae will need to use functions like OFFSET or INDEX or something combining ADDRESS and INDIRECT. In each case you have to use functions not generally knowing their dependencies for sure in advance, and being recalculated the volatile mode therefor if 'AutoCalculate' is on.

That doesn't matter much if there are a few hundred rows. It will cause inefficiency and probably additional problems if there are thousands of rows.

I should better suggest that you find a way to avoid the need of this kind of rearranging the data. Generally data keeping sheets are organised "one row per item". Whats the reason for what you think you need three rows per item in a different sheet?

See this attachment.

I can tell you "how to do it", and add that the solution comes with disadvantages. Any solution by formulae will need to use functions like OFFSET or INDEX or something combining ADDRESS and INDIRECT. In each case you have to use functions not generally knowing their dependencies for sure in advance, and being recalculated the volatile mode therefor if 'AutoCalculate' is on.

That doesn't matter much if there are a few hundred rows. It will cause inefficiency and probably additional problems if there are thousands of rows.

I should better suggest that you find a way to avoid the need of this kind of rearranging the data. Generally data keeping sheets are organised "one row per item". Whats the reason for what you think you need three rows per item in a different sheet?

See this attachment.

.

EDIT1 (regarding the comment below):
There is this very short tutorial concerning the question in a better structured forum. You need to read the grayed part.
(I could also try to explain, but not being a native speaker of English I would probably not succeed with a precise wording.)

Come back if you want additional information concerning the specific way I did it. You probably need a deeper understanding if you want to parametrtise the process.