Specifying Named Ranges in Multiple Sheets in Single Wrkbook

kcleere1127

New Member
Joined
Oct 22, 2005
Messages
11
In the formula below you see I am adding up the instances where:

Atlanta_Inst="Complete"
and
Atlanta_Int="Complete"
When
AtlantaWho="N"

=SUMPRODUCT(('1'!Atlanta_Inst="Complete")*('1'!Atlanta_Int="Complete")*('1'!AtlantaWho="N"))

This formula refers to sheet 1 ('1'!) of a workbook. This workbook has 5 worksheets and I want to add total all the "N"'s in all 5 worksheets when as the conditions are met.

I know I could do this to get it to add the ranges in the 1st, 2nd, 3rd, 4th and 5th sheets:

=SUMPRODUCT(('1'!Atlanta_Inst="Complete")*('1'!Atlanta_Int="Complete")*('1'!AtlantaWho="N"))+SUMPRODUCT(('2'!Atlanta_Inst="Complete")*('2'!Atlanta_Int="Complete")*('2'!AtlantaWho="N"))

What I'd like to know is if there is a way to reference the multiple worksheets without adding more +SUMPRODUCT statements. Such as (just so you know what I'm thinking:

=SUMPRODUCT(('1'!'2'!'3'!'4'!'5'!Atlanta_Inst="Complete")*('1'!'2'!'3'!'4'!'5'!Atlanta_Int="Complete")*('1'!'2'!'3'!'4'!'5'!AtlantaWho="N"))

I'm sure what I did there is probably improper but I hope it tells you that I simply want to reference the ranges in the 5 sheets with a shorter formula.

Thanks in advance for any advise.

Ken
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

XLGibbs

Well-known Member
Joined
Feb 25, 2005
Messages
2,446
Why not simply have the same sumproduct formula on each of the five sheets and aggregate them with a simple Sum formula at your desired result location.

Otherwise,I am unaware of a formula that will look on each sheet for 1 named range, as that would certainly cause issues (it would likely be some variation of a circular reference of some kind anyway, since each of the sheets has identical names)
 

kcleere1127

New Member
Joined
Oct 22, 2005
Messages
11
Thanks for the input. Excel does in fact allow the same range name across multiple worksheets and it differntiates with by the sheet numbers or whatever you actually name the tabs.

As far as putting my formula in each sheet...I'd consider that but I'd actually have to have 28 formulas on each of the 5 sheets as thats how many sections per sheet there are. That and inexperienced users will be entering data into the sheets so I want to keep the intelligence iand maintenance on one locked summary tab.

Thanks again for the input.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,758
Messages
5,574,071
Members
412,566
Latest member
TexasTony
Top