How to escape double quote in INDIRECT()

[rob2]

In a spreadsheet I have the following formula:
=MINIFS($‘2019’.B$1:B$150");$‘2019’.E$1:E$150;"="&C4;$‘2019’.B$1:B$150;">"&TODAY())

This works fine!
I want the tab (in this case ‘2019’) to be red from another cell (in my case D2), so I started to build the statement using INDIRECT():

=MINIFS(INDIRECT($D$2&".B$1:B$150");$‘2019’.E$1:E$150;"="&C4;$‘2019’.B$1:B$150;">"&TODAY())

I changed the function for all ocurrences of the tab ‘2019’ (which is in A2:

=MINIFS(INDIRECT($A$2&".B$1:B$150");INDIRECT($A$2&".E$1:E$150");"="&C4;INDIRECT($A$2&".B$1:B$150");">"&TODAY())

This works…

For speed I would like to have only one INDIRECT().

I can’t get it to work, though.

Question:
Can the whole string in one go, so MINIFS(INDIRECT(…))?

[SM_Riga]

Hello @rob2

To escape double quotes you need to use triple or quadruple quotes, it depends, please see image for reference. But I do not understand, what you want to escape in $'2019'.E$1:E$150 part. If you want to replace 2019 here to $D$2, just do the same as in the first INDIRECT - INDIRECT($D$2&".E$1:E$150")

— Edit #1 2019-01-10 —

Regarding you rephrased question - no, it is not possible to use INDIRECT in such a manner. INDIRECT translates reference to the cell or the cell range, given as a text string or as a formula’s text string result, to the internal reference for the corresponding cell/range. It just tells Calc that “Sheet1.A1” is not just some text, but a reference to A1 cell on Sheet1. So, including all this $D$2&".B$1:B$150";$'2019'.E$1:E$150;"="&C4;$'2019'.B$1:B$150;">"&TODAY() long string inside INDIRECT makes no sense, cause it is not cell or range address. You need to refer each range individually. Also you can consider using Named Ranges for better formula readability