I’m having trouble following the doc to understand the new LET() function introduced in v24.8. It’s behaving in ways that I don’t understand and the formulas are producing results that I think they shouldn’t be able to. Let me explain.
In my Calc file, the first sheet is named “InputLines”. Column A has a few hundred lines of dictated text. Column A is named “InputLine” so that I can refer to it in formulas with that name.
The second sheet is named “Parse” and it has formulas using REGEX() to extract info from the InputLines. Column B has date entries and that column is named “LineDate”. The other parsed-out data columns have names beginning with “Line...” so they are together in the list of names in the Manage Names window.
All of the formulas in the Parse sheet are wrapped with the same IF function:
=IF( InputLine = "" , "-"
. . .
)
This does nothing other than to flag which of the input lines are empty and show tha the formula is there. This makes it easy at the bottom of the sheet to see how far down I have copied the formulas so that I know how far to copy when I have made a change or when the number of input lines exceeds how far the formulas have been copied.
I tried to get an extremely simple instance of LET to work. First, to make sure that my names etc. were all working as expected, I created this simple formula:
=IF( InputLine = "" , "-" ,
LineDate
)
This works as expected. It displays the date that is parsed out from the InputLine by the REGEX formula in the LineDate column. And as expected, the IF first argument is TRUE so the second argument is skipped and the third argument is calculated.
So then I tried putting that LineDate inside a LET:
=IF( InputLine = "" , "-" ,
LET(
ThisDate , LineDate ,
ThisDate
)
)
In my interpetation of the LET manual page, the local-to-LET variable ThisDate should be given the calculated value of LineDate, which in my previous calculation was the date value extracted from the corresponding input line. Then that variable should be presented as the calculation result.
But it isn’t. What I get instead of the value of LineDate is simply “-” — it’s as if somehow the LET function is influencing the calculation of the IF, which makes no sense to me at all.
How can I get LET to do what I expect in this situation? What am I not understanding in the manual page? What is the manual page not explaining that it should? Is the name LineDate interpreted differently inside the LET than it had been without the LET? Even if that’s the case, why did I get “-” instead of an error. Or is it a bug in this new feature? But how could even a bug in LET influence the IF calculation?
I’m baffled. Any suggestions?
