4

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?

migrated from superuser.com Nov 6 '17 at 6:43

This question came from our site for computer enthusiasts and power users.

1

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

=match(B3, indirect(B2), 0)
  • But I want a drop down list. I tried your code, the cell becomes empty. – GeneCode Nov 7 '17 at 3:07
  • 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 Nov 7 '17 at 9:27

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.