<html>
    <head>
      <base href="https://bugs.documentfoundation.org/">
    </head>
    <body><span class="vcard"><a class="email" href="mailto:mikekaganski@hotmail.com" title="Mike Kaganski <mikekaganski@hotmail.com>"> <span class="fn">Mike Kaganski</span></a>
</span> changed
          <a class="bz_bug_link 
          bz_status_RESOLVED  bz_closed"
   title="RESOLVED NOTABUG - Incorrect work =FORMULA()"
   href="https://bugs.documentfoundation.org/show_bug.cgi?id=138994">bug 138994</a>
          <br>
             <table border="1" cellspacing="0" cellpadding="8">
          <tr>
            <th>What</th>
            <th>Removed</th>
            <th>Added</th>
          </tr>

         <tr>
           <td style="text-align:right;">Status</td>
           <td>NEW
           </td>
           <td>RESOLVED
           </td>
         </tr>

         <tr>
           <td style="text-align:right;">Resolution</td>
           <td>---
           </td>
           <td>NOTABUG
           </td>
         </tr></table>
      <p>
        <div>
            <b><a class="bz_bug_link 
          bz_status_RESOLVED  bz_closed"
   title="RESOLVED NOTABUG - Incorrect work =FORMULA()"
   href="https://bugs.documentfoundation.org/show_bug.cgi?id=138994#c2">Comment # 2</a>
              on <a class="bz_bug_link 
          bz_status_RESOLVED  bz_closed"
   title="RESOLVED NOTABUG - Incorrect work =FORMULA()"
   href="https://bugs.documentfoundation.org/show_bug.cgi?id=138994">bug 138994</a>
              from <span class="vcard"><a class="email" href="mailto:mikekaganski@hotmail.com" title="Mike Kaganski <mikekaganski@hotmail.com>"> <span class="fn">Mike Kaganski</span></a>
</span></b>
        <pre>This is not a bug.

Your spreadsheet uses a mix of settings:

* Formula syntax is set to Calc A1 at Options|Calc|Formula [1];
* Reference syntax for string reference is set to Excel A1 at
Options|Calc|Formula|Detailed Calculation Settings [2].

The former setting makes all formulas (including those in A2) to be in Calc
syntax (with references like "$Sheet.A1"), which is correctly converted to
string by FORMULA function; but then you try to use part of that string in
INDIRECT, which is affected by the latter setting (and is expecting references
like "Sheet!A1").

It works as intended. FORMULA is expected to return the formula as it is shown
in formula bar, without any additional transformations. If you rely on Excel
syntax like that, then you need to change the former setting to Excel A1.

[1] <a href="https://help.libreoffice.org/7.0/en-US/text/shared/optionen/01060900.html">https://help.libreoffice.org/7.0/en-US/text/shared/optionen/01060900.html</a>
[2]
<a href="https://help.libreoffice.org/7.0/en-US/text/shared/optionen/detailedcalculation.html">https://help.libreoffice.org/7.0/en-US/text/shared/optionen/detailedcalculation.html</a></pre>
        </div>
      </p>


      <hr>
      <span>You are receiving this mail because:</span>

      <ul>
          <li>You are the assignee for the bug.</li>
      </ul>
    </body>
</html>