Dear professionals,
I have worksheets named: Jan15, Feb15 and Mar15
and I want to sum a specific cell (A1) across these worksheets using =SUM(Jan15:Mar15!A1)
but it doesn't work as excel read Jan15 as a cell reference.
How do you solve this problem without changing the worksheet name?
Warmest regards,
Eric
Does it work with the single quote marks included?
Formula:
Please Login or Register to view this content.
BSB
Works OK for me:
=SUM(Jan15:Mar15!A1)
Not sure what that means?Originally Posted by Ericng
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
I think what it means is there is a cell with the reference JAN15. As there would be with MAR15.
BSB
Fails for me on 2010 without the additional single apostrophes. JAN15 is indeed a valid cell reference.
Regards
I tried the single quote but doesnt work too.
I wrote it this way: =sum('Jan15':'Dec15'!A1) correct me if I am wrong.
Jan15 is a form of like A1 in my excel 2015.
OK, I see. I first tested in Excel 2002 (my default version) which works but I just tested in Excel 2010 which didn't work.
Excel thinks JAN15 is a cell reference.
Either change the sheet names so there's no confusion or add the ' as suggested.
Try it like this...
=SUM('Jan15:Dec15'!A1)
Once change the sheets name ( first and last) - Try something like sheet1- sheet10 for applying the formula =sum(sheet1:sheet10!A1). If the formula get working then change the sheet name as Jan15 - Mar15.
If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Is this because that version didn't go as far as column JAN, then?
Regards
Yes.
I use Excel 2002 as my default version as it has many custom toolbars.
Thanks Tony Valko for helping you solved my problem.
Thanks XOR LX for helping me to further clarify my question.
Another question will be:
Since the cell A1 will be changing often because of inserting row or column in each worksheets (assuming I consistently change insert new number of row across all worksheet),
How do formulate it without having to change cell manually each time.
Providing you make any row/column insertions in all relevant sheets simultaneously (i.e. when all sheets are grouped), then the 3D reference will update appropriately.
Regards
Don't forget BadlySpelledBuoy!
Thanks BadlySpelledBouy!
@XOR LX: I tried but it fixed at A1.
No problem. You're not the first and I doubt you'll be the last...
In order for XOR LX's suggestion to work you have to insert the row on ALL relevant sheets at the same time. Activate the Jan15 sheet, hold shift then click on the Dec15 sheet tab at the bottom of the screen. You should see all sheets between these two have no changed colour where you have them all selected at once.
NOW insert the new column and the formula should update as necessary.
BSB
Can you tell me precisely how you "tried"? Do you know what I meant by "grouped"?Thanks BadlySpelledBouy!
@XOR LX: I tried but it fixed at A1.
Regards
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks