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"))
Last edited by jimstrongy; 02-10-2012 at 05:09 PM.
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.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
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.Originally Posted by NBVC
Any other ways to go around this issue?
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)
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
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?
Do you mean that "sales" and "tax" can both be found in column E? or are they in separate columns?
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Same columns.
Try:
=SUM(SUMIF(INDIRECT("'"&TEXT(A1,"mm-dd-yy")&"'!C:C"),{"sales","tax"},INDIRECT("'"&TEXT(A1,"mm-dd-yy")&"'!E:E")))
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Thanks NBVC., that works!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks