google-sheetsfilterrowmultiple-columnsexcel-indirect

Reference a sheet range dynamically inside formula by using a cell value

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.

Solution

  • 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