HOW TO LOCK FORMULA SOMASE AND UPDATE IT BY ITSELF?

Copper Contributor

I made a SOMASE formula to pull data from 2 spreadsheets, the problem is that I always need to replace the files with current ones with new information, and the SOMASE formula in the base spreadsheet does not automatically update the information whenever I replace the files, how do I do it?

The base files that I pull the information from will always be named deposito1 and deposito2.

=SOMASE([deposito1.xlsx]Folha1!$A:$A;@A:A;[ deposito1.xlsx]Folha1!$E:$E)

and

=SOMASE([deposito2.xlsx]Folha1!$A:$A; A2; [deposito2.xlsx] Folha1!$E:$E)

6 Replies

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

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

I tried using your formula above, but it still gave an error, could it be that a wrong character was entered?

Below is my formula again but updated (Microsoft Office 2016)

=SOMASE([almox.xls]Sheet1!$C:$C;A2;[almox.xls]Sheet1!$H:$H)

=SOMASE([snd.xls]Sheet1!$C:$C;A2;[snd.xls]Sheet1!$H:$H)

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

@NikolinoDE 

The error occurs, the formula appears (#name):

 

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

 

Captura de tela 2024-03-20 054901.png

@jrsilverio 

It seems like the #NAME? error occurs because the SOMASE function might not be recognized in your version of Excel. The correct function name for summing with conditions in Excel is typically SUMIFS, not SOMASE. Let's correct the formula accordingly:

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

 

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

Make sure to replace "almox.xls" and "snd.xls" with the actual file names of your Excel workbooks. Also, ensure that the referenced workbooks are open while using the INDIRECT function in your formulas. If the workbooks are closed, the formula may return a #REF! error.