5

In Excel, at B3, I use data validation of List and entered:

=INDIRECT(B2)

In B2 I put the name of the range I defined. This works well. I enter the name of the range in B2, and B3 is updated with the list of the corresponding range.

Now I transfer my Excel worksheet to Google Sheets. And it no longer works. I just get the error:

Invalid.

And there aren't any items in the dropdown.

I read some old answers about this problem; one suggested to use ARRAYFORMULA like so:

=ARRAYFORMULA(INDIRECT(B2))

but I still get an error:

Please enter a valid range

and it didn't allow me to save the validation.

What can I do?

Improve this question

1 Answer 1

Reset to default
1

Use a custom formula validation (not List), and enter the following formula:

=match(B3, indirect(B2), 0)
Improve this answer
2
  • But I want a drop down list. I tried your code, the cell becomes empty.
    – GeneCode
    Commented Nov 7, 2017 at 3:07
  • 1
    Well in this case, make the "List from a range" data validation from a new range (that is currently empty on your spreadsheet), and on the top of that new range put the =INDIRECT(B2) formula. No way around it. Works as tested.
    – ttarchala
    Commented Nov 7, 2017 at 9:27

Your Answer

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

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