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.
Best Answer