Move existing SUM downward when IMPORTRANGE expands

Question

I'm building a complex Google Sheets document and for one sheet I use the function IMPORTRANGE. It's very handy and imports new rows from another sheet when they are added. The problem is this:

After the last row of the import from my IMPORTRANGE I add a small 'Total' calculation to SUM all of the above numbers.

What I want is that the 'total' calculation row moves down when the IMPORTRANGE expands with more rows.

Is there any way to achieve this dynamically / automatically so that my 'Total' calculation moves down and keeps summing?

Answer 1

Use the Google Sheets array handling feature

Example

Lets assume that A1 has the spreadsheet key required by IMPORTRANGE, that eh source spreadsheet has only the rows required to hold the data (no blank rows) and that the formula is set in A2

={IMPORTRANGE(A1,"data!A:A"},SUM(INDIRECT("A2:A"&(ROW()-1))}

See the google-sheets-arrays wiki for references and related questions.

Answer 2

If I understand your question correctly (i.e. that you have a source Sheet in which the SUM lives in row X and if you add rows above it then your IMPORTRANGE call will not follow along (as it would if it was in the same document) and subsequently you lose the reference to that SUM cell) then one way around this is to give that SUM cell a name (right-click on the cell and select Define named range from the menu then label the cell MY_SUM (or whatever you wish to name it).

Once that's done you can then reference it in the IMPORTRANGE by its name like:

=IMPORTRANGE(URL, "'sheet name'!MY_SUM")

Now if you insert rows above that cell it doesn't matter and the reference will persist.