I'm trying to get a unique list of values in a master sheet based on a list of values that appear in column A of other sheets. The tricky part is that every time a new column is added in the master sheet, that will be another new sheet that I have to grab values from. Here's a quick example (very simplified) of the general layout I'll have:
So the idea is that a new copy of a 'List' template will be created a user with new data that needs to be brought into the master sheet, and the name of that new sheet will be added as a column in Sheet1; I don't want to have to update the formula that populated Column A whenever a new list is added as a new column (which is done after a sheet of the same name is created). At that point, the unique values from that new sheet should be added to Column A of Sheet1. Not only must the values be unique in the new sheet, but they have to be unique across all sheets. In the example sheet I provided above, the value 456 appears in List 1 and List 2, but it should only appear once in Sheet1.
I'm familiar with how to reference cells/ranges from other sheets, as well as how to reference another sheet based on a value from another cell, but I don't know how I would necessarily be able to iterate through all the sheets indicated by the column names provided in Sheet1.
=INDIRECT("'"&B1&"'!A:A")where B1 is "List 1" like in your example. not sure if this helps tho.