2

Say I have two named ranges,

  • team_a_coaches
  • team_a_players

Is it possible to have another named range,

  • team_a

and define it as the union of the first two named ranges?

The goal would be that if I edited a cell or added a cell for team_a_players, team_a would automatically update.

Here is a demo spreadsheet. Please make a copy and you'll see that I'm trying to use these named ranges for data validation.

Improve this question

1 Answer 1

Reset to default
2

while answer would be yes for cases when you use named ranges in formulas, this is not possible in data validation, because data validation doesn't support custom formulas for input and also doesn't support multi-range inputs.

therefore the only workaround would be to use some hidden helping column with a formula:

=SORT({team_a_coaches; team_a_players}, 1, 1)

and then create named range for it and use it in data validation

see demo sheet here: https://docs.google.com/spreadsheets/d/

for formula cases examine this gif: https://i.stack.imgur.com/kOoIB.gif

enter image description here

Improve this answer
2
  • Thanks for this response, but to me (maybe I misunderstand), this seems like this solution the array into one value. I want to keep the separate values in the final named range so I can use it for data validation. I amended my question with a demo spreadsheet.
    – mattliu
    Sep 6, 2018 at 2:45
  • 1
    @mattliu ohh, I see now... however, thats not possible to do in data validation - only in formula like: =SORT({team_a_coaches;team_a_players},1,1) which you can put in some hidden column and then make data validation to look for that. see demo here: docs.google.com/spreadsheets/d/…
    – user0
    Sep 6, 2018 at 8:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.