Add cell from every sheet starting with "SR"

[tonywatsonhelp]

Hi Everyone,
Not sure if this can be done but I need a formula that can add cell A1 every sheet that starts with the letters "SR"
any ideas?

Must be a formula not allowed to use VBA on this.

Thanks

Tony

 

[steve the fish]

Easy with VBA, very much doubt it possible without.

 

[CyrusTheVirus]

Perhaps try Power Query? Try following steps

1) Open a blank excel workbook
2) Click Data > Get Data > From File > From Workbook
3) Find workbook and open it
4) Select the main workbook folder and then click edit
5) Then click the filter of the 'name' column then 'text filters', then 'begins with' then type 'SR' (case sensitive i think) in the field and click 'ok'
6) Then highlight the first two columns, then right click one of the headers and choose to remove other columns
7) Highlight first column then at the top click 'remove rows', then 'remove duplicates'
8) Highlight second column, click group by, then use sum operation, then select correct column, then click 'ok'

what do you think? worth a try?

 

[Aladin Akyurek]

=SUMPRODUCT(N(INDIRECT("'"&SheetList&"'!A1")),--(LEFT(SheetList,2)="SR"))

where SheetList refers to a range which lists all of the sheets of the current workbook.

 

[CyrusTheVirus]

Nice one Aladin. How does the N function work, looks like it converts value errors to values.

 

[Aladin Akyurek]

Nice one Aladin. How does the N function work, looks like it converts value errors to values.

N() returns the input as is if the input is a number: A1 = 5, N(A1) >> 5.

N() returns 1 if the input is the logical TRUE: A1 = TRUE, N(A1) >> 1. When A1 = FALSE, N(A1) >> 0.

N() return the input an error value input as is if the input is an error value: for example, A1 = #REF !, N(A1) >> #REF !.

N() call in the SUMPRODUCT formula we have uses the first case. SUMPRODUCT cannot access the results of INDIRECT("'"&SheetList&"'!A1"). We call this phenomenon dereferencing problem, the solution of which requires a second round of evaluation, that is, we need to evaluate the results of INDIRECT("'"&SheetList&"'!A1"). This is done here with feeding the results to N() that makes them visible to the outer SUMPRODUCT.

Hope this helps.

 

[CyrusTheVirus]

N() returns the input as is if the input is a number: A1 = 5, N(A1) >> 5.

N() returns 1 if the input is the logical TRUE: A1 = TRUE, N(A1) >> 1. When A1 = FALSE, N(A1) >> 0.

N() return the input an error value input as is if the input is an error value: for example, A1 = #REF !, N(A1) >> #REF !.

N() call in the SUMPRODUCT formula we have uses the first case. SUMPRODUCT cannot access the results of INDIRECT("'"&SheetList&"'!A1"). We call this phenomenon dereferencing problem, the solution of which requires a second round of evaluation, that is, we need to evaluate the results of INDIRECT("'"&SheetList&"'!A1"). This is done here with feeding the results to N() that makes them visible to the outer SUMPRODUCT.

Hope this helps.

Thank you Aladin. Guess, I'm still curious as to how it is converting the value errors to a number. When I evaluate the formula what is delivered to the N function is a series of #VALUE ! errors.

 

[Aladin Akyurek]

Thank you Aladin. Guess, I'm still curious as to how it is converting the value errors to a number. When I evaluate the formula what is delivered to the N function is a series of #VALUE ! errors.

Try to evaluate using F9...

=SUMPRODUCT(N(INDIRECT("'"&SheetList&"'!A1")),--(LEFT(SheetList,2)="SR"))

=SUMPRODUCT(N({10;15;25;40}),{0;1;1;0})

=SUMPRODUCT({10;15;25;40},{0;1;1;0})

>> 40

Thus, select first the INDIRECT("'"&SheetList&"'!A1") part on the formula bar and appy F9.

Select the --(LEFT(SheetList,2)="SR") part and apply F9.

Select the N({10;15;25;40}) now and apply F9.

Select now =SUMPRODUCT({10;15;25;40},{0;1;1;0}) and apply F9.

 

[CyrusTheVirus]

Try to evaluate using F9...

=SUMPRODUCT(N(INDIRECT("'"&SheetList&"'!A1")),--(LEFT(SheetList,2)="SR"))

=SUMPRODUCT(N({10;15;25;40}),{0;1;1;0})

=SUMPRODUCT({10;15;25;40},{0;1;1;0})

>> 40

Thus, select first the INDIRECT("'"&SheetList&"'!A1") part on the formula bar and appy F9.

Select the --(LEFT(SheetList,2)="SR") part and apply F9.

Select the N({10;15;25;40}) now and apply F9.

Select now =SUMPRODUCT({10;15;25;40},{0;1;1;0}) and apply F9.

Ah, i see. I wasn't using F9, but instead just pressing evaluate which shows the #VALUE ! error, because when using F9 it doesn't show as errors, interesting. Thank you again, Aladin.

 

[Aladin Akyurek]

Ah, i see. I wasn't using F9, but instead just pressing evaluate which shows the #VALUE ! error, because when using F9 it doesn't show as errors, interesting. Thank you again, Aladin.

You are welcome. Evaluate Formula seems to suffer also from the dereferencing problem. After N() does the second round evaluation that Evaluate sees the right thing.