Indirect Across Sheets labeled 1 (2), 1 (3), 2, 3, 3 (1) etc

ssdailypro

New Member
Joined
Sep 4, 2010
Messages
12
For Excel 2003 and higher. I am currently using the formula to sum across sheets like this (thank you Aladin for this tip!) on a summary sheet:
{=SUM(IF(ISERR(N(INDIRECT("'"&DAY($A10)+{0,0.2,0.3,0.4,0.5}&"'!C7"))),0,N(INDIRECT("'"&DAY($A10)+{0,0.2,0.3,0.4,0.5}&"'!C7"))))}

This has worked great for the sheets labeled 1,2,3,4 and for any other sheets labeled 1.1, 1.2, 1.3, 2, 3 etc...

However, I am now looking for a similar formula that would be able to handle sheets with 1 (2), 1 (3), 2, 3, etc.

A10 = the day of the month on the summary sheet
C7 = the cell reference on the individual sheets for that month

Thank you for your help.

Scott
 
Last edited:

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
For Excel 2003 and higher. I am currently using the formula to sum across sheets like this (thank you Aladin for this tip!) on a summary sheet:
{=SUM(IF(ISERR(N(INDIRECT("'"&DAY($A10)+{0,0.2,0.3,0.4,0.5}&"'!C7"))),0,N(INDIRECT("'"&DAY($A10)+{0,0.2,0.3,0.4,0.5}&"'!C7"))))}

This has worked great for the sheets labeled 1,2,3,4 and for any other sheets labeled 1.1, 1.2, 1.3, 2, 3 etc...

However, I am now looking for a similar formula that would be able to handle sheets with 1 (2), 1 (3), 2, 3, etc.

A10 = the day of the month on the summary sheet
C7 = the cell reference on the individual sheets for that month

Thank you for your help.

Scott

Are these 1 (2), 1 (3), 2, 3, etc. just arbitrary sheet names or related somehow to a date in A10 in some fashion?
 

ssdailypro

New Member
Joined
Sep 4, 2010
Messages
12
They are related to A10 on the summary page. Sometimes I have multiple sheets for the same day and I want to sum that days totals as it relates to cell reference A10. (currently this is the system I have to work with and can not be changed)

Then day 2 is cell reference A11 and so on until the end of the month.

Thank you for your help.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
They are related to A10 on the summary page. Sometimes I have multiple sheets for the same day and I want to sum that days totals as it relates to cell reference A10. (currently this is the system I have to work with and can not be changed)

Then day 2 is cell reference A11 and so on until the end of the month.

Thank you for your help.

Does the plugging in...

"'"&DAY($A10)&" ("&{2,3,4,5}&")"&"'!C7"

yields the desired effect?
 

ssdailypro

New Member
Joined
Sep 4, 2010
Messages
12

ADVERTISEMENT

Yes, I did try that...and for example with tab 1 (2) it would give me a negative cell reference "-1".

I assume it is because it is reading the "(2)" as -2 and adding it with "1" resulting in negative one sheet reference (formula resulting in an error message).

I tried (and failed) at using your previous formula and combining it with text formula so it reads the parentheses as text vs. a negative number.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Yes, I did try that...and for example with tab 1 (2) it would give me a negative cell reference "-1".

I assume it is because it is reading the "(2)" as -2 and adding it with "1" resulting in negative one sheet reference (formula resulting in an error message).

I tried (and failed) at using your previous formula and combining it with text formula so it reads the parentheses as text vs. a negative number.

If we need to consider a possible series of sheets like 1, 1 (2), 1 (3), 1 (4), and 1 (5) for DAY($A10) where the day value is 1, then I'd expect:

Control+shift+enter, not just enter...

=SUM(IF(ISERR(N(INDIRECT("'"&SUBSTITUTE(DAY($A10)&" ("&{0,2,3,4,5}&")"," (0)","")&"'!C7"))),0,N(INDIRECT("'"&SUBSTITUTE(DAY($A10)&" ("&{0,2,3,4,5}&")"," (0)","")&"'!C7"))))

to succeed as intended.
 

ssdailypro

New Member
Joined
Sep 4, 2010
Messages
12
Aladin~

Yes! This worked perfectly. Thank you so much for your time and effort with this - it is truly appreciated.

Have a great day!

Scott
 

Watch MrExcel Video

Forum statistics

Threads
1,122,867
Messages
5,598,542
Members
414,245
Latest member
allyciv

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