MegaSack DRAW - This year's winner is user - rgwb
We will be in touch
This one is foxing me -
I have a workbook with a bunch of numbered sheets. I want to be able to create a summary that combines various sheets. eg.
I have sheets named 1 to 10 with values in cell a1
On Sheet 'Summary' I want to list some sheet numbers in column A (say a1:a5, some of which could be blank) then have b1 report the sum of values in a1 on each of the entered sheets.
So B1 should be something along the lines of an array formula like:
=sum(iferror(indirect(a1:a5&"!a1"),0))
but that fails when it expands the array reference. Any thoughts?
I'd just simplify it by pulling in the values on each sheet individually, then sum them.
so in B1 type:
=IFERROR(INDIRECT(A1&"!A1"),0)
and copy down to B5
then in C1 type:
=SUM(B1:B5)
Does it have to be an array function?
Cheers reggie - that's sort of what I've ended up with. At the moment I've just expanded the array manually rather than use the array function, but it makes maintenance more long winded eg when someone wants to summarise more sheets than I've allowed for, and just seems less refined.
How's your VBA?
Yeah that's the other option portlyone
YGM OP
Edit: Well, you would have if your profile emailer worked... Mine does if you want me to send you [s]the[/s] a solution 😀