0

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:

https://docs.google.com/spreadsheets/d/1yHDQXzOXtXbEQ7Jb3JtF93ycKq6RZ1nLRXZhsSySaKE/edit#gid=55113996

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.

  • On your example can you provide a tab with the expected result. Anyway, in case the values of line 1 are always the name of the tab, you can do something like this : =INDIRECT("'"&B1&"'!A:A") where B1 is "List 1" like in your example. not sure if this helps tho. – pjmg Aug 25 at 11:21
0
  • paste this script into your spreadsheet:

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

  • paste this formula in Sheet1!B1 cell:

    =TRANSPOSE(FILTER(SNAME(1), SNAME(1)<>SNAME(0)))

    0

  • paste this formula in Sheet1!B2 cell:

    =IF(B1<>"", INDIRECT(B1&"!A:A"), )

    0

  • paste this formula in Sheet1!A2 cell:

    =ARRAYFORMULA(SORT(UNIQUE(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE(
    IF(B2:Z<>"", "♦"&B2:Z, )),,999^99)),,999^99), "♦")))))

    0

  • spreadsheet demo


note: you can add onEdit trigger in order to recalculate the script

  • Thanks! I've learned a lot from your answer. This got me really close. One complication to the situation, though, is that I need to reserve the table contents (B2:Z) for other data I want to pull in. To illustrate what I mean, I created a "Desired output" sheet that should hopefully clear it up. While your solution worked, I'm wondering if there's a way to be able to avoid having to use the contents of the table that this creates just to populate the unique values in Column A. Any thoughts? docs.google.com/spreadsheets/d/… – JDishman Oct 12 at 2:06

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.