Ask Your Question
0

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

asked 2018-08-07 21:16:34 +0200

Thomarse gravatar image

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.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2018-08-07 23:35:46 +0200

Lupp gravatar image

updated 2018-08-08 10:25:16 +0200

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.

edit flag offensive delete link more

Comments

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.

Thomarse gravatar imageThomarse ( 2018-08-08 09:07:24 +0200 )edit

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

Lupp gravatar imageLupp ( 2018-08-08 10:25:43 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-08-07 21:16:34 +0200

Seen: 168 times

Last updated: Aug 08 '18