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

[ssdailypro]

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

 

[Aladin Akyurek]

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]

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]

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]

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]

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]

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

 

[Aladin Akyurek]

Aladin~

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

You are welcome. Thanks for providing feedback.

Have a great day!

Scott

You too...