HOW TO LOCK FORMULA SOMASE AND UPDATE IT BY ITSELF?

jrsilverio 

If you're using Microsoft Office 2016, the SOMASE function might not recognize the dynamic file references directly. In that case, you can still achieve the desired functionality by using the INDIRETO function to create dynamic references.

Here's how you can modify your formulas:

=SOMASE(INDIRECT("[" & "almox.xls" & "]Sheet1!$C:$C"), A2, INDIRECT("[" & "almox.xls" & "]Sheet1!$H:$H"))

=SOMASE(INDIRECT("[" & "snd.xls" & "]Sheet1!$C:$C"), A2, INDIRECT("[" & "snd.xls" & "]Sheet1!$H:$H"))

Note: the INDIRECT function in Excel requires the referenced workbooks to be open in order to work properly. This function essentially evaluates a text string as a reference to a cell or range, but it cannot access closed workbooks.

If the referenced workbooks are closed, the INDIRECT function will return a #REF! error. To avoid this, you need to ensure that the source workbooks (almox.xls and snd.xls in your case) are open while using the INDIRECT function in your formulas.

If it's not practical to keep all the source workbooks open at all times, you may need to consider alternative approaches, such as using VBA (Visual Basic for Applications) macros to perform the data retrieval and calculation tasks, or consolidating the data from multiple workbooks into a single workbook. These approaches can provide more flexibility and automation compared to using formulas with dynamic references to closed workbooks.