Google-sheets – Indirect named range

google sheetsworksheet-function

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?

Best Answer

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

=match(B3, indirect(B2), 0)

Related Solutions

Google Sheets – How to Create Named Ranges Based on Formulas

You could use a simple formula to build a string defining the Range, name that location, and then access it with the INDIRECT Function in your formulas. E.g. for your question above.

Put in any cell (e.g. B3) a formula like:
```
= "Sheet!$A$1:$A$" & B2
```
Name Cell B3 as "items" as a named range.
Then whenever you would have used "items" in a formula just use
```
INDIRECT(items)
```

It is not quite the same as a dynamic named range, but almost as neat and certainly as effective.

Google Sheets – How to Filter Based on Dropdown List of Months

To convert YYYY, MM, DD to an actual Date object, you need the DATE command. The following works:

=filter(A6:F371,A6:A371>=DATE(2015,1,1),A6:A371<=DATE(2015,1,31))

But I see you want to filter based on dropdown entry. For this I would use MONTH function. For example, suppose cell J10 has validation rule: list of items

01 (January), 02 (February), 03 (March), and so on

Then your filter in J11 could be

=filter(A6:F371,MONTH(A6:A371)=VALUE(LEFT(J10,2)))

The idea here is that the names of month help the user in selection, but the filter uses the numeric format (month number).

Best Answer

Related Solutions

Google Sheets – How to Create Named Ranges Based on Formulas

Google Sheets – How to Filter Based on Dropdown List of Months

Related Topic