HOW TO LOCK FORMULA SOMASE AND UPDATE IT BY ITSELF?

jrsilverio Note that the INDIRECT function requires the other workbooks to be open!

jrsilverio 

To ensure that your SUMIF formulas update automatically when you replace the source files (deposito1.xlsx and deposito2.xlsx) with new information, you can use dynamic references that automatically adjust to the new file names.

One way to achieve this is by using cell references to store the file names, allowing you to easily update the formulas without manually editing them each time. You can set up a cell in your base spreadsheet to contain the file names, and then reference these cells in your SUMIF formulas.

Here's how you can modify your formulas:

1. Set up cell references for file names: In your base spreadsheet, designate cells where you'll input the file names (e.g., A1 for deposito1.xlsx and A2 for deposito2.xlsx).
2. Update SUMIF formulas: Instead of hardcoding the file names in your SUMIF formulas, reference the cells where you've input the file names.

For the first formula, you can use:

=SUMIFS(INDIRECT("[" & A1 & "]Folha1!$E:$E"), INDIRECT("[" & A1 & "]Folha1!$A:$A"), A:A)

For the second formula, you can use:

=SUMIFS(INDIRECT("[" & A2 & "]Folha1!$E:$E"), INDIRECT("[" & A2 & "]Folha1!$A:$A"), A2)

These formulas use the INDIRECT function to dynamically construct the file references based on the file names entered in cells A1 and A2.

Now, when you replace the source files with new information and update the file names in cells A1 and A2, the SUMIF formulas will automatically reflect the changes without requiring manual edits to the formulas. The text was created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and like it!

This will help all forum participants.