How can I reference a sheet range by using a cell value?

i.e

This is a working formula

=filter(dump!1:1000,dump!G:G = "Active")

However, I need to reference the sheet's G:G range dynamically by using a cell value. Let's assume my cell A1 contains "G:G" as the value inside it.

My formula would be something like

=filter(dump!1:1000,"dump!"&A2 = "Active")

..but of course it doesn't work because I'm not using the syntax properly.

CodePudding user response：

Use INDIRECT:

=filter(dump!1:1000,"dump!"&INDIRECT(A1) = "Active")

CodePudding user response：

use:

=FILTER(dump!1:1000, INDIRECT("dump!"&A1) = "Active")

but keep in mind that G:G needs to have exactly 1000 rows otherwise you will face ARRAY_ error