Changing the sheet selection in a function?

[cjwenngatz]

I have a summary page that is collecting some lines of data from 340
different worksheets.

Essentially it's a SUMIF for data in every sheet that is then pulled
to a central summary sheet. Rather than retype the different sheet
numbers in every row of the formula - is there a way to automate this?

Thanks!

 

[Don Guillett]

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

 

[cjwenngatz]

Thanks Don, I really appreciate the offer - however the data within
this workbook is confidential and cannot be emailed out.

Any chance you have a link to an outline as to how to complete this?

 

[Don Guillett]

To give a solution I would have to create a wb and test. Surely you can do
that much for yourself in creating a dummy info workbook.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Thanks Don, I really appreciate the offer - however the data within
this workbook is confidential and cannot be emailed out.

Any chance you have a link to an outline as to how to complete this?

 

[cjwenngatz]

Hi Don - That's very true, I can do that.

I did however run into the following formula that should work:

SUMPRODUCT(SUMIF(INDIRECT("'"&A5:A344&"'!C16:C300"),E4,INDIRECT
("'"&A5:A344&"'!W16:W300")))

For some reason it is coming up with #REF sign, and I'm not sure why.
All the sheet numbers are lined through A5 and A344 of the summary
page.

Any thoughts?
Thanks!

 

[Don Guillett]

This works for a list in col F
'=SUMPRODUCT(SUMIF(INDIRECT(OFFSET(F1,,,COUNTA(F:F))&"!B1"),"PMI",INDIRECT(OFFSET(F1,,,COUNTA(F:F))&"!A3")))

This works for a range using a defined name of the sheet names
'=SUMPRODUCT(SUMIF(INDIRECT(ms&"!$j1:j21"),"xx",INDIRECT(ms&"!k1:k21")))
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Hi Don - That's very true, I can do that.

I did however run into the following formula that should work:

SUMPRODUCT(SUMIF(INDIRECT("'"&A5:A344&"'!C16:C300"),E4,INDIRECT
("'"&A5:A344&"'!W16:W300")))

For some reason it is coming up with #REF sign, and I'm not sure why.
All the sheet numbers are lined through A5 and A344 of the summary
page.

Any thoughts?
Thanks!