Google-sheets – How to pull the page number and the number of images listed from one sheet and then populate cells in another sheet accordingly

formulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-queryworksheet-function

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

Thanks in advance!

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

Best Answer

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

Related Solutions

Google-sheets – way to automatically duplicate a large number of Google Sheets, name them, then copy their URLs to another sheet

Here is a (stand-alone) script that attempts to do this. An important caveat: scripts are subject to quotas, one of which is: no more than 250 spreadsheets created per day. So the script will be halted before finishing the job if you run it in the present form. You may want to change the upper bound to 671+250, and rerun the script with different starting values on different days.

The logic is simple: the file number 670 is used as a prototype for all of them, via makeCopy method. Each newly created file is then modified via SpreadsheetApp, and its URL is recorded in master sheet.

function copy() {
  var masterId = ' ID of your master spreadsheet';
  var fileId = ' ID of your 670 file ';

  var master = SpreadsheetApp.openById(masterId);
  var masterSheet = master.getSheetByName('list of URLs');
  var file = DriveApp.getFileById(fileId);
  for (var i = 671; i < 8192; i++) { 
    var newFile = file.makeCopy('file'+i);
    var ss = SpreadsheetApp.open(newFile);
    var sheet = ss.getSheets()[0];    // assuming you want the first sheet
    sheet.getRange('A6').setValue(i);
    masterSheet.getRange('L' + (i+1)).setValue(ss.getUrl());
  }
}

Google-sheets – Track highest number in a cell over a series of recalculations

To keep track of running maximum of the content of some cell, one needs a script that runs every time the cell changes value. Such a script could be triggered by the same button you are using to force recalculation, although a delay (Utilities.sleep(1000), time in milliseconds) may be necessary to make sure the recalculation runs before this script. Or, if recalculation was caused by an edit to the sheet, the function could be simply named onEdit to run on every edit.

In this example, B1 is used to record the maximum of values that were placed in A1.

function recordMax() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange("A1:B1");
  var values = range.getValues()[0];
  range.setValues([[values[0], Math.max(values[0], values[1])]]);
}

Best Answer

Related Solutions

Google-sheets – way to automatically duplicate a large number of Google Sheets, name them, then copy their URLs to another sheet

Google-sheets – Track highest number in a cell over a series of recalculations

Related Topic