Ask Your Question
0

How to escape double quote in INDIRECT()

asked 2019-01-10 19:59:30 +0100

-rob- gravatar image

updated 2019-01-10 21:00:28 +0100

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(......))?

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2019-01-10 20:43:36 +0100

updated 2019-01-10 21:35:42 +0100

Hello @-rob-

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")

escaping_double_quotes

--- 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

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-01-10 19:59:30 +0100

Seen: 101 times

Last updated: Jan 10