# SUM across other worksheets (without hardcoding their names)

I've got a spreadsheet for my organisation's management accounts. Each project has a worksheet, setting out income and expenditure by financial year. This is aggregated together in a few sheets including a Profit & Loss.

I want to devise a formula that will add up a particular cell from all the project worksheets, but without hardcoding the worksheet names in the formula. That's because I add and remove projects, and don't want the formula to break.

Is there something equivalent to "add up this cell in all the worksheets with a name beginning with R-"?

Alternatively, can I create a list from a row that contains all the project names, and then use an ARRAYFORMULA to cycle through them finding the appropriate value and summing them all?

Here's some background on the spreadsheet...

The project worksheets look like this:

Accounting code | 2018-19 | 2019-20 | 2020-21
----------------------------------------------
Income general  | £20,000 | £25,000 | £15,000


The P&L columns for that project like this:

Accounting code | Project name
| Budget for year | Budget to date | Actual to date
-------------------------------------------------
Income general  | £20,000         | £16,000        | £15,463


The 'budget for year' figure is pulled out as follows:

=IFERROR(VLOOKUP($A9,INDIRECT("'"&N$4&"'!$A5:$Z100"),MATCH(v_CurYr,INDIRECT("'"&N$4&"'!$A4:$E4")),FALSE),0)  Where$A9 is that row's accounting code, N\$4 references the project name, and v_CurYr is a cell giving the current year (2018-19).

For the ARRAYFORMULA, we could use the row that has the headers for the project names, but the names appear in every 4th column - the others in between being blanks.

• pls, share a copy of your sheet Commented Mar 29, 2019 at 12:23

the names appear in every 4th column

=TRANSPOSE(FILTER(A1:1, MOD(COLUMN(A1:1), 4)=0))

all the worksheets with a name beginning with R-

=QUERY(TRANSPOSE(FILTER(A1:1, MOD(COLUMN(A1:1), 4)=0)), "where Col1 contains 'R-'", 0)

can I create a list from a row that contains all the project names, and then cycle through them finding the appropriate value and summing them all?

=IFERROR(INDIRECT(A5&"!A9"))+
IFERROR(INDIRECT(A6&"!A9"))+
IFERROR(INDIRECT(A7&"!A9"))

• Thanks. The QUERY/TRANSPOSE bits pull out the list of project names. But I meant something different by the next step. Rather than hardcoding the list of projects, as you have done, I want to dynamically use the list produced by the QUERY. If I were coding this in appscript it would be a loop, to cycle through the project names and for each used INDEX and MATCH to find the value, and add it to the running total. I don't know if that's possible in a gsheets formula? Commented Mar 29, 2019 at 17:08
• I would call it semi-hardcoding coz as you can see A7 is empty eg. if it becomes dynamically not empty then it will properly sum up. if not, it stays ignored. yeah, with a script it could be possible. Commented Mar 29, 2019 at 17:14