CALC: Make forms from 1st, 2nd, 3rd and so on rows from another sheet

[jamiol]

Hi,

LibreOffice 4.2.6.3 English & Windows 8.1 64-bit English.

I’m trying to make form-system with LibreOffice Calc (or any other LibreOffice program), but starting to get a bit dispirited on the googleing because I’m not exactly sure wheter what I’m trying is even possible. I’ll try to explain what I’m after in the next paragraph. Any help is highly appreciated

So, it would be a Calc file with two Sheets:

Sheet 1 will include information in cells in style off: A1 name; B1 date; C1 info; D1 info; E1 etc;;;;;; and then A2,B2… will have another bunch of data and so on.

Sheet 2 will be the actual presentation which will be printed and will include boxes, lines and stationary information. Numerous cells in Sheet2 will refrence information from Sheet1, but the one print cycle would only need info from one row e.g. A1, B1, C1…

Now, if there would be only one row of data I would know how to do this. “=Sheet1.A1” and so on to every cell, and print away. But the Sheet2 will have at least 20+ of these cell’s and changing them all by hand every time would be highly inefficient and make the whole thing pretty much pointless.

Since the row is the only thing changing, I’m thinking that making the reference something along the lines of “=Sheet1.A%VARIABLE%” and then the number would be in another Sheet1 cell and the user would just change it by one every time the printing is finished. Though I don’t know if this kind of “reference inside of a reference” is possible?

So basically I’m looking a way to “reference inside a reference” and/or some other idea to make this thing a bit more useful. A completly automatic way of fething the data row by row would ofcourse be the ideal solution, perhaps making the print task as .pdf instead of directly printing (either every row to their own file or to the same), and then just printing the .pdf’s after everything is finished.

// Reading this, it’s bit of a mouthful. I hope I made myself understood.

[Lupp]

(Not asked for:) Use first row (at least) for column headers (and for meta info if wanted). Start with data in, say, row 11 and call this the first dataset (ID=1).

(Asked for:) Simply tell your Sheet2 which dataset of Sheet1 it shall process. The cell, say Sheet2.$A$1 where you enter the selecting ID=SomeDataSetNumber will not be in the ‘Print Range’ set for Sheet2 assumably.

Editing: Errors corrected!

You may refer to, say, cell BSomeDataSetNumber then by =OFFSET($Sheet1.$A$1;10+$A$1-1;column(B$)-1).
The 10 telling that data start with an offset of 10 rows (0+10=10) $A$1 will introduce the number of the dataset. Note: offsets count from 0 (zero). Therefore the “-1”. The next parameter is introducing the column offset in a way we are used to having chosen “A1” style addressing.

(There are other means, in specific the INDEX() function which may produce your results. There are reasons for my preferring OFFSET().)

Refering to an empty (blank) cell of Sheet1 you will get 0 (zero) in Sheet2 this way. If you want an empty cell there too in this case you may ask another question.

Editing (2):

I attach an example that is not exemplary in any detail. It shall just demonstrate how to use OFFSET() or INDEX() to variably reference data depending on another reference ($Report.$A$1 in the example). At least I thought this was what you looked for.

[jamiol]

Any chance you could offer a detailed “for example”? I’m not that familiar with calc and have trouble following your train of thought.

[jamiol]

Hmm… Starting to wonder if I should just collect the data with CALC and then export it as .csv and figure out a way to automate this thing with python.