0

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 – user0 Mar 29 at 12:23
0

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)

enter image description here

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"))

0

  • 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? – Tom Chance Mar 29 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. – user0 Mar 29 at 17:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.