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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,018
Messages
5,834,973
Members
430,330
Latest member
drAli77

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top