Calc - how to fill a series from multiple rows that all reference the same row

[Thomarse]

I have cells in a few rows that all reference one row on another sheet:

=Sheet2.A1

=Sheet2.B1

=Sheet2.C1

When I select these cells and drag the fill handle to fill the cells below, Calc fills them like this:

=Sheet2.A1

=Sheet2.B1

=Sheet2.C1

=Sheet2.A4

=Sheet2.B4

=Sheet2.C4

=Sheet2.A7

=Sheet2.B7

=Sheet2.C7

etc.

I guess it’s doing this because each new row is three cells below the original row.

This is not the behaviour I want - I want to fill them in a series, like this:

=Sheet2.A1

=Sheet2.B1

=Sheet2.C1

=Sheet2.A2

=Sheet2.B2

=Sheet2.C2

=Sheet2.A3

=Sheet2.B3

=Sheet2.C3

=Sheet2.A4

=Sheet2.B4

=Sheet2.C4

etc.

Any ideas how to do this?

I’d like to use either a formula or a manual way of doing this - I’m not familiar with macros so would rather avoid if possible, please.

[Lupp]

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.

[Thomarse]

That’s brilliant, thank you! It’s only a few hundred rows, so shouldn’t be a problem. It’s for an automated invoice spreadsheet - the data needs to be laid out like that for aesthetics, so I can show a breakdown of costs. The second sheet it’s referencing stores all the data on a single row but you can’t display it all on a single page.

Do you think you could explain a little what you’ve done? I’m not quite sure exactly what’s going on with your formula.

[Lupp]

To do it for pretty printing (“Form Report”) is IMO the only acceptable reason for this kind of design.