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

Question

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.

score 1 · Answer 1 · answered Feb 07 '22 at 15:23

1

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

answered Feb 07 '22 at 15:23

Rafa Guillermo

Thank you! INDIRECT() was what I was looking for. – Sergiu Poenaru Feb 08 '22 at 10:33

score 1 · Accepted Answer · answered Feb 07 '22 at 23:05

1

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

answered Feb 07 '22 at 23:05

player0

1

Thank you! INDIRECT() was what I was looking for. – Sergiu Poenaru Feb 08 '22 at 10:33

2 Answers2