0

<<<< EDIT #1 >>>>

Ok here is the problem described newly with a Spreadsheet as an example. https://docs.google.com/spreadsheets/d/1P5o-UpQyLNAnfW0W2dIPacYsmBUrIJ6OT790HehxLjE/edit?usp=sharing

The PDF from the DESIGN tab in DIN A5 size as result: https://drive.google.com/file/d/16gcIzOpOs4gFDuPD4OpUlHms0oJFhe2y/view?usp=sharing

In the PDF you can see that the number of the pages do not corresponded with the the data source in the brown cells. Page 2 should also have the data source from row 2 from the DATA Sheet
-> Page 2; Top text: should be DATA B2
-> Page 2; text XY: should be DATA A2

instead it is (when I copy the pages down the DESIGN tab)
-> Page 2; Top text: DATA B33
-> Page 2; text XY: DATA A33
and so on.

I hope it is clearer now and there is a solution. Sorry for my bad description and English language.

<<<< End of Edit #1 >>>>

I try to achieve the following, but with no luck so far.

I have a Google spreadsheet with data in the first tab which is called "DATA". The data is in column/row AE2, AE3, AE4 and so on. Now I have a design tab (in the same spreadsheet) called "DESIGN" where I import with "=DATA!AE2" the source data. But in this design tab, there are 4 more rows in-between (because it should appear as DIN A5 when printing) and when I copy the the whole DIN A5 "page" within the "DESIGN" tab at the end as next page, the source data is not increment by 1. Instead it increments the 4 more rows extra which are in-between. So the formula is now "=DATA!AE7" instead of "=DATA!AE3".

How can I prevent this and always increment by 1. There maybe an easy solution?

3
  • If the three in-between rows in DESIGN are expected to be blank, perhaps you can just increase row height so that the spacing is the same, but no extra rows are needed? Right-click a row number and choose Resize row. Commented May 24, 2021 at 14:38
  • unfortunatly they are not empty. i forgot to mention this correctly. the space in-between is filled with text. so i need to do this with a formular unfortunatly
    – jenar gun
    Commented May 24, 2021 at 16:57
  • Please edit your question so that it portrays the scenario correctly. Show an example by inserting tables. Consider sharing a publicly editable sample spreadsheet with realistic-looking data, and showing your hand-entered expected results there. Commented May 24, 2021 at 18:56

1 Answer 1

0

Try this:

  • Cell B2: =indirect(address(E1;2;4;1;"DATA"))
  • Cell A4: =indirect(address(E1;1;4;1;"DATA"))

Then copy these two formula into the respect cells on each "page".


ADDRESS(): returns a cell reference as a string.
The key in this example is using the page number as the row value. As you copy the formula into subsequent cells on pages, the formula will pickup the page number in Column E; the page number will increment by 1, and so the row number will increment as well.

INDIRECT(): Returns the content of the cell reference created by ADDRESS().

1
  • thank you! it worked! simple and perfect!
    – jenar gun
    Commented May 26, 2021 at 6:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.