error when using INDIRECT inside FILTER

Question

I've been banging my head with this for hours, I saw a couple example with INDIRECT and some that even had FILTER containing INDIRECT, which is exactly what I'm trying to do, but for a reason I don't know, it's not working.

I'm trying to do the following:

=FILTER('Form Responses 1'!D2:F, ('Form Responses 1'!D2:D=TODAY())+(ISBLANK('Form Responses 1'!E2:E)=FALSE()))

But I want to divide it into 2 part, one cell (F1) will contain the second argument:

('Form Responses 1'!D2:D=TODAY())+(ISBLANK('Form Responses 1'!E2:E)=FALSE())

And the second cell will contain the whole formula using it, like this:

=FILTER('Form Responses 1'!D2:F, INDIRECT(F1))

What I get is an error:

"FILTER has mismatched range sizes. Expected row count: 116. column count: 1. Actual row count: 1, column count: 1."

Any idea? I've attached a photo to further explain. []

BTW, the weird language is Hebrew

Answer 1

Short answer

The Google Sheets spreadsheet engine is not able to evaluate a text as a formula. Related question: Is there a way to evaluate a formula that is stored in a cell?

Extended answer

The problem is with =FILTER('Form Responses 1'!D2:F, INDIRECT(F3)) and according to the screenshot, is because F3 is a blank cell, so INDIRECT(F3) returns and error instead of the required argument.

It looks that instead of F3, you want to use F1 which contains:

('Form Responses 1'!D2:D=TODAY())+(ISBLANK('Form Responses 1'!E2:E)=FALSE())

...but the above text is not a proper argument for INDIRECT as it requires text in the form of cell or range address.

It's worth noting that the Google Sheets spreadsheet engine doesn't have a function to evaluate text as formulas. INDIRECT is only able to return a cell or range (multiple cells) reference.