I am trying to create a sheet that has a list of areas, and then has a dropdown next to each area name that lets you select one item found in that area.

The contents of each area are in Col A of a sheet that is titled with the name of that area.

I am trying to use Indirect to point to the name of the area, in Col A, and then create a Dropdown using the contents of Col A on the sheet referenced.

The formula I am trying to use is =INDIRECT("'"&A1&"'!A:A")

When I put this into B1, for example, it spits out a list of all the items on the tab that shares a name with the contents of A1.

However, when I try to use this formula to define the range for the dropdown list in Data Validation, it says that it is an invalid range.

Example of what I am trying to make:

And then for example there would be a tab named "Area 1", and Col A would just be a list of all the items in that Area.

The use case here is that I am trying to create a sheet to track encounters in a video game where you're only allowed to acquire the first character you encounter in area named are of the game, and I would like to be able to apply it to any number of different datasets (games), so I would prefer to avoid having to hardcode or specifically limit any names or ranges.

CodePudding user response：

such a formula is not supported under data validation (DV supports only such formulae whose output is boolean TRUE/FALSE)

to do what you want you will need to use that formula in some helper column and feed DV from the resulting range

CodePudding user response：

Suggestion:

You can use App Script to automatically create a data validation with values referencing from other sheets. Below is the script that creates a data validation in Area column after adding a value in the Location column.

Note:

Value you add in the Location column must exactly match the name of the reference sheet

Code

function onEdit() {
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  var sheet = ss.getSheetByName('Main'); 

  var range = sheet.getRange(2,1,sheet.getLastRow() -1,1).getValues(); //contains list of areas
  
  for(var i = 0; i < range.length; i  ){
    var sh = ss.getSheetByName(range[i]);
    var values = sh.getRange(1,1,sh.getLastRow(),1);
    var itemrange = sheet.getRange(2 i,2);
    var rule = SpreadsheetApp.newDataValidation().requireValueInRange(values).build(); 
    itemrange.setDataValidation(rule);
  }

}

sample output:

references:

https://developers.google.com/apps-script/reference/spreadsheet/data-validation-builder https://developers.google.com/apps-script/guides/triggers