Google Sheets – Import Cells Incrementally When Copy Pasting

google sheets

<<<< 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?

Best Answer

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().

Related Topic