# How to pull the page number and the number of images listed from one sheet and then populate cells in another sheet accordingly?

Sorry, I didn't know how to summarize it in the title. Basically, I have a spreadsheet where I'm keeping track of the number of images that need to be on each page of a document I'm working on.

On the first tab, I have a sheet to keep track of how many images are on each page. On the second tab, I have a sheet to write out what the image concept is for each image. To keep track of which page and image number it is, I've been writing them as [page #] - [image #] on the second tab.

This is super time-consuming, and so far the best solution I've come up with is to copy and paste a column of 5 rows that say x-1, x-2, x-3, etc and then select them and do a find and replace x for the page number.

I'm wondering if there's a way I can pull the page number from Tab 1 and the number of images listed and then populate cells in Tab 2 accordingly.

e.g. - Cell with page number 11 + cell with image number 4 would produce 4 rows with:

• 11-1
• 11-2
• 11-3
• 11-4

EDIT: Here's a link to a copy of the spreadsheet

• create a hidden auxiliary sheet (let's call it `AUX`)
• paste this formula into A1 and drag down (when done - hide sheet)

``````=IF(LEN(Main!A1), ARRAYFORMULA(IFERROR(TO_TEXT(TRANSPOSE(
SPLIT(Main!A1&"-"&ROW(INDIRECT("A1:A"&Main!B1))&" ", " "))), )), )``````

• paste this formula into cell A1 on `Concepts` sheet

``````={QUERY({AUX!A1:A8;AUX!B1:B8;AUX!C1:C8;AUX!D1:D8;AUX!E1:E8},
"select Col1 where Col1 is not NULL", 0);
QUERY({AUX!A9:A;AUX!B9:B;AUX!C9:C;AUX!D9:D;AUX!E9:E},
"select Col1 where Col1 is not NULL", 0)}``````

Ianthe, looks like you may be all set. But I left a new page ("Page-Illustrations Generator [Erik]") with a two-column array approach...

In A1:

`=ArrayFormula(TRANSPOSE(SPLIT(JOIN("",REPT(Main!A2:A&"-/",Main!B2:B)),"/")))`

In B1:

`=ArrayFormula(IF(A1:A="","",COUNTIFS(ROW(A1:A),"<="&ROW(A1:A),A1:A,A1:A)))`

I just resized the columns, then aligned A:A right and B:B left.

• nice one, I dared to improve it a bit – user0 Feb 8 '19 at 23:04
• Dare away. Looks good. – Erik Tyler Feb 8 '19 at 23:27
• formula for 63 input rows - paste in `Concepts!A1` cell (format column `A` as `Automatic`) :

``````=ARRAYFORMULA({
IF(LEN(Main!A2),  SPLIT(Main!A2 &"-"&ROW(INDIRECT("A1:A"&Main!B2)) &" ", " "), );
IF(LEN(Main!A3),  SPLIT(Main!A3 &"-"&ROW(INDIRECT("A1:A"&Main!B3)) &" ", " "), );
IF(LEN(Main!A4),  SPLIT(Main!A4 &"-"&ROW(INDIRECT("A1:A"&Main!B4)) &" ", " "), );
IF(LEN(Main!A5),  SPLIT(Main!A5 &"-"&ROW(INDIRECT("A1:A"&Main!B5)) &" ", " "), );
IF(LEN(Main!A6),  SPLIT(Main!A6 &"-"&ROW(INDIRECT("A1:A"&Main!B6)) &" ", " "), );
IF(LEN(Main!A7),  SPLIT(Main!A7 &"-"&ROW(INDIRECT("A1:A"&Main!B7)) &" ", " "), );
IF(LEN(Main!A8),  SPLIT(Main!A8 &"-"&ROW(INDIRECT("A1:A"&Main!B8)) &" ", " "), );
IF(LEN(Main!A9),  SPLIT(Main!A9 &"-"&ROW(INDIRECT("A1:A"&Main!B9)) &" ", " "), );
IF(LEN(Main!A10), SPLIT(Main!A10&"-"&ROW(INDIRECT("A1:A"&Main!B10))&" ", " "), );
IF(LEN(Main!A11), SPLIT(Main!A11&"-"&ROW(INDIRECT("A1:A"&Main!B11))&" ", " "), );
IF(LEN(Main!A12), SPLIT(Main!A12&"-"&ROW(INDIRECT("A1:A"&Main!B12))&" ", " "), );
IF(LEN(Main!A13), SPLIT(Main!A13&"-"&ROW(INDIRECT("A1:A"&Main!B13))&" ", " "), );
IF(LEN(Main!A14), SPLIT(Main!A14&"-"&ROW(INDIRECT("A1:A"&Main!B14))&" ", " "), );
IF(LEN(Main!A15), SPLIT(Main!A15&"-"&ROW(INDIRECT("A1:A"&Main!B15))&" ", " "), );
IF(LEN(Main!A16), SPLIT(Main!A16&"-"&ROW(INDIRECT("A1:A"&Main!B16))&" ", " "), );
IF(LEN(Main!A17), SPLIT(Main!A17&"-"&ROW(INDIRECT("A1:A"&Main!B17))&" ", " "), );
IF(LEN(Main!A18), SPLIT(Main!A18&"-"&ROW(INDIRECT("A1:A"&Main!B18))&" ", " "), );
IF(LEN(Main!A19), SPLIT(Main!A19&"-"&ROW(INDIRECT("A1:A"&Main!B19))&" ", " "), );
IF(LEN(Main!A20), SPLIT(Main!A20&"-"&ROW(INDIRECT("A1:A"&Main!B20))&" ", " "), );
IF(LEN(Main!A21), SPLIT(Main!A21&"-"&ROW(INDIRECT("A1:A"&Main!B21))&" ", " "), );
IF(LEN(Main!A22), SPLIT(Main!A22&"-"&ROW(INDIRECT("A1:A"&Main!B22))&" ", " "), );
IF(LEN(Main!A23), SPLIT(Main!A23&"-"&ROW(INDIRECT("A1:A"&Main!B23))&" ", " "), );
IF(LEN(Main!A24), SPLIT(Main!A24&"-"&ROW(INDIRECT("A1:A"&Main!B24))&" ", " "), );
IF(LEN(Main!A25), SPLIT(Main!A25&"-"&ROW(INDIRECT("A1:A"&Main!B25))&" ", " "), );
IF(LEN(Main!A26), SPLIT(Main!A26&"-"&ROW(INDIRECT("A1:A"&Main!B26))&" ", " "), );
IF(LEN(Main!A27), SPLIT(Main!A27&"-"&ROW(INDIRECT("A1:A"&Main!B27))&" ", " "), );
IF(LEN(Main!A28), SPLIT(Main!A28&"-"&ROW(INDIRECT("A1:A"&Main!B28))&" ", " "), );
IF(LEN(Main!A29), SPLIT(Main!A29&"-"&ROW(INDIRECT("A1:A"&Main!B29))&" ", " "), );
IF(LEN(Main!A30), SPLIT(Main!A30&"-"&ROW(INDIRECT("A1:A"&Main!B30))&" ", " "), );
IF(LEN(Main!A31), SPLIT(Main!A31&"-"&ROW(INDIRECT("A1:A"&Main!B31))&" ", " "), );
IF(LEN(Main!A32), SPLIT(Main!A32&"-"&ROW(INDIRECT("A1:A"&Main!B32))&" ", " "), );
IF(LEN(Main!A33), SPLIT(Main!A33&"-"&ROW(INDIRECT("A1:A"&Main!B33))&" ", " "), );
IF(LEN(Main!A34), SPLIT(Main!A34&"-"&ROW(INDIRECT("A1:A"&Main!B34))&" ", " "), );
IF(LEN(Main!A35), SPLIT(Main!A35&"-"&ROW(INDIRECT("A1:A"&Main!B35))&" ", " "), );
IF(LEN(Main!A36), SPLIT(Main!A36&"-"&ROW(INDIRECT("A1:A"&Main!B36))&" ", " "), );
IF(LEN(Main!A37), SPLIT(Main!A37&"-"&ROW(INDIRECT("A1:A"&Main!B37))&" ", " "), );
IF(LEN(Main!A38), SPLIT(Main!A38&"-"&ROW(INDIRECT("A1:A"&Main!B38))&" ", " "), );
IF(LEN(Main!A39), SPLIT(Main!A39&"-"&ROW(INDIRECT("A1:A"&Main!B39))&" ", " "), );
IF(LEN(Main!A40), SPLIT(Main!A40&"-"&ROW(INDIRECT("A1:A"&Main!B40))&" ", " "), );
IF(LEN(Main!A41), SPLIT(Main!A41&"-"&ROW(INDIRECT("A1:A"&Main!B41))&" ", " "), );
IF(LEN(Main!A42), SPLIT(Main!A42&"-"&ROW(INDIRECT("A1:A"&Main!B42))&" ", " "), );
IF(LEN(Main!A43), SPLIT(Main!A43&"-"&ROW(INDIRECT("A1:A"&Main!B43))&" ", " "), );
IF(LEN(Main!A44), SPLIT(Main!A44&"-"&ROW(INDIRECT("A1:A"&Main!B44))&" ", " "), );
IF(LEN(Main!A45), SPLIT(Main!A45&"-"&ROW(INDIRECT("A1:A"&Main!B45))&" ", " "), );
IF(LEN(Main!A46), SPLIT(Main!A46&"-"&ROW(INDIRECT("A1:A"&Main!B46))&" ", " "), );
IF(LEN(Main!A47), SPLIT(Main!A47&"-"&ROW(INDIRECT("A1:A"&Main!B47))&" ", " "), );
IF(LEN(Main!A48), SPLIT(Main!A48&"-"&ROW(INDIRECT("A1:A"&Main!B48))&" ", " "), );
IF(LEN(Main!A49), SPLIT(Main!A49&"-"&ROW(INDIRECT("A1:A"&Main!B49))&" ", " "), );
IF(LEN(Main!A50), SPLIT(Main!A50&"-"&ROW(INDIRECT("A1:A"&Main!B50))&" ", " "), );
IF(LEN(Main!A51), SPLIT(Main!A51&"-"&ROW(INDIRECT("A1:A"&Main!B51))&" ", " "), );
IF(LEN(Main!A52), SPLIT(Main!A52&"-"&ROW(INDIRECT("A1:A"&Main!B52))&" ", " "), );
IF(LEN(Main!A53), SPLIT(Main!A53&"-"&ROW(INDIRECT("A1:A"&Main!B53))&" ", " "), );
IF(LEN(Main!A54), SPLIT(Main!A54&"-"&ROW(INDIRECT("A1:A"&Main!B54))&" ", " "), );
IF(LEN(Main!A55), SPLIT(Main!A55&"-"&ROW(INDIRECT("A1:A"&Main!B55))&" ", " "), );
IF(LEN(Main!A56), SPLIT(Main!A56&"-"&ROW(INDIRECT("A1:A"&Main!B56))&" ", " "), );
IF(LEN(Main!A57), SPLIT(Main!A57&"-"&ROW(INDIRECT("A1:A"&Main!B57))&" ", " "), );
IF(LEN(Main!A58), SPLIT(Main!A58&"-"&ROW(INDIRECT("A1:A"&Main!B58))&" ", " "), );
IF(LEN(Main!A59), SPLIT(Main!A59&"-"&ROW(INDIRECT("A1:A"&Main!B59))&" ", " "), );
IF(LEN(Main!A60), SPLIT(Main!A60&"-"&ROW(INDIRECT("A1:A"&Main!B60))&" ", " "), );
IF(LEN(Main!A61), SPLIT(Main!A61&"-"&ROW(INDIRECT("A1:A"&Main!B61))&" ", " "), );
IF(LEN(Main!A62), SPLIT(Main!A62&"-"&ROW(INDIRECT("A1:A"&Main!B62))&" ", " "), );
IF(LEN(Main!A63), SPLIT(Main!A63&"-"&ROW(INDIRECT("A1:A"&Main!B63))&" ", " "), )})``````

``````=ARRAYFORMULA(IFERROR(TRANSPOSE(SPLIT(JOIN( , REPT(Main!A2:A&"-♥", Main!B2:B)), "♥"))&
IF(ROW(A2:A)="", , COUNTIFS(ROW(INDIRECT(
"A1:A"&COUNTA(TRANSPOSE(SPLIT(JOIN( , REPT(Main!A2:A&"♥", Main!B2:B)), "♥"))))),
"<="&ROW(INDIRECT(
"A1:A"&COUNTA(TRANSPOSE(SPLIT(JOIN( , REPT(Main!A2:A&"♥", Main!B2:B)), "♥"))))),
TRANSPOSE(SPLIT(JOIN( , REPT(Main!A2:A&"♥", Main!B2:B)), "♥")),
TRANSPOSE(SPLIT(JOIN( , REPT(Main!A2:A&"♥", Main!B2:B)), "♥")))), ))``````