formatting cells and tabs for Indirect formula.

[jimstrongy]

I'm using this formula and I have problem with indirect because my tabs have dates and I can't find any way to format my cells in column A to read the tabs but in same time being recognoizable as a date. Any ide how to change cell format or tab format so its understandable what date it is and that formula picks it up?

SUMIF(INDIRECT(A1&"!C:C"),"sales",INDIRECT(A1&"!E:E"))

[NBVC]

Try:

=SUMIF(INDIRECT(TEXT(A1,"mmmyy")&"!C:C"),"sales",INDIRECT(TEXT(A1,"mmmyy")&"!E:E"))

change the format "mmmyy" to match the format of the tab name.

[jimstrongy]

Try:

=SUMIF(INDIRECT(TEXT(A1,"mmmyy")&"!C:C"),"sales",INDIRECT(TEXT(A1,"mmmyy")&"!E:E"))

change the format "mmmyy" to match the format of the tab name.

NBVC none of that works my tab originaly had 10-01-11 and I custom formated cell to mm-dd-yy. That didn't work and I tried changing tab names to 10 01 11 and custom formating cell to accept that but no luck. Only way it works is if I put 100111 but that really doesn't help human eye recognize the date on the tab.
Any other ways to go around this issue?

[NBVC]

Try:

=SUMIF(INDIRECT("'"&TEXT(A1,"mm-dd-yy")&"'!C:C"),"sales",INDIRECT("'"&TEXT(A1,"mm-dd-yy")&"'!E:E"))

with tab named 10-01-11 (assuming that is Oct 1, 2011)

[jimstrongy]

Try:

=SUMIF(INDIRECT("'"&TEXT(A1,"mm-dd-yy")&"'!C:C"),"sales",INDIRECT("'"&TEXT(A1,"mm-dd-yy")&"'!E:E"))

with tab named 10-01-11 (assuming that is Oct 1, 2011)

Thanks NBVC, that worked great, just a quick question besides "sales" that I'm adding here if I want to add one more criteria "tax" is there a quick way to adjust this formula to sum "sales" and "tax" together?

[NBVC]

Do you mean that "sales" and "tax" can both be found in column E? or are they in separate columns?

[jimstrongy]

Do you mean that "sales" and "tax" can both be found in column E? or are they in separate columns?

Same columns.

[NBVC]

Try:

=SUM(SUMIF(INDIRECT("'"&TEXT(A1,"mm-dd-yy")&"'!C:C"),{"sales","tax"},INDIRECT("'"&TEXT(A1,"mm-dd-yy")&"'!E:E")))

[jimstrongy]

Thanks NBVC., that works!