Pull unique values from other sheets as new sheets keep getting added in Google Sheets

Question

I'm trying to generate a list of unique items from column A of an indeterminate number of sheets. The idea is that over time, more sheets will be added and I don't want to have to update formulas to account for the new sheets.

Test/example spreadsheet https://docs.google.com/spreadsheets/d/193ZTAbn8R-HjwDn2LE2eDDVJk_LbY4V5P9J6ynES6wo/edit#gid=0

In the example, sheets List1, List2, and List3 are the lists from which I want to create a combined list (in sheet "CombinedList") with the items that appear in Column A of each sheet. As the "Desired Outcome" sheet shows, I want to end up with a table that pulls in values associated with each item from each list. I'm ok with manually adding the name of new sheets into Row 1 as they get created; it's just the generation of the list of items in Column A that has me stuck.

I was originally doing something like the following,

=ARRAYFORMULA(UNIQUE(QUERY({'List1'!A2:A;'List2'!A2:A}, 
 "select Col1 where Col1 is not null")))

but instead of having to write out all the names of the lists to query, I'd like to using something like INDIRECT() to reference the sheets that get listed in Row 1. However, I've read several places that INDIRECT and ARRAYFORMULA don't play nice with each other or something.

Answer 1

you can use this script:

function SNAME(option) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet()
var thisSheet = sheet.getName(); 
if(option === 0){                  // ACTIVE SHEET NAME =SNAME(0)
return thisSheet;
}else if(option === 1){            // ALL SHEET NAMES   =SNAME(1)
var sheetList = [];
ss.getSheets().forEach(function(val){
   sheetList.push(val.getName())
});
return sheetList;
}else if(option === 2){            // SPREADSHEET NAME  =SNAME(2)
return ss.getName();

}else{
return "#N/A";                     // ERROR MESSAGE
};
};

function onEdit() { 
var sheet = SpreadsheetApp.getActive().getSheetByName('Master Sheet');  
var src = sheet.getRange("A1");
var str = src.getValue(); 
var cell = sheet.getRange("A2"); 
cell.setFormula(str);
}

then paste this into A1 of your master sheet:

=ARRAYFORMULA("=ARRAYFORMULA(QUERY({"&TEXTJOIN("; ", 1, 
 FILTER(SNAME(1), SNAME(1)<>SNAME(0))&"!A2:B, "&
 FILTER(SNAME(1), SNAME(1)<>SNAME(0))&"!C2:C&"&
 FILTER(SNAME(1), SNAME(1)<>SNAME(0))&"!A1")&
 "}, ""select Col1,max(Col2) where Col1 is not null group by Col1 pivot Col3""))")

this will cover all your needs to not edit the formula by adding references if new sheets are added. the only drawback is a recalculation of sheet name script... to do so you need to dismantle A1 formula for example by adding ' before the leading = pressing enter and then removing it to fix the formula

spreadsheet demo