0

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.

Tab 1 Tab 2

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

Thanks in advance!

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

0
  • 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)}

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 at 23:04
  • Dare away. Looks good. – Erik Tyler Feb 8 at 23:27
0
  • 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))&" ", " "), )})

enter image description here

0

=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)), "♥")))), ))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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