Hello,
I have a workbook that records shelf life performance scores. There is a worksheet for each day of the month from 10-01-10 to 10-16-10 and the sheets are named in this fashion. I've inserted a Summary sheet as the last sheet and tried to add the values in cell K32 on every sheet in cell A1 of the summary sheet. I'm using this formula:
=SUM(10-01-10:10-16-10!K32)
Every time I hit enter after entering this formula I get a pop-up window that says "Update Values: 10." Why is this happening? Does it have something to do with the way the sheets are named?
Ultimately, what I need to do is average the values between K2:K31 across all sheets, so if anyone has a better suggestion on how to do that I would be ecstatic. I hope I've given enough info.
Thanks for your help!
Try this:
=SUM('10-1-10:10-16-10'!K32)
=AVERAGE('10-1-10:10-16-10'!K32)
Notice the little apostrophes?
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Yep, I see 'em, but that didn't work. I get a #REF! error, and it changes the formula to look like this:
=AVERAGE('10-01-10:[10-16-10]10-16-10'!K32)
Any other suggestions?
Yeah, let's see the file. Click GO ADVANCED and use the paperclip icon to post up a desensitized copy of your workbook.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Here you go - thanks again for your help.
Last edited by whattheheck; 11-11-2010 at 04:59 PM.
Fix your sheet names. They all have spaces in them.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Here, run this macro to fix it quickly.
Option Explicit Sub FixSheetNames() Dim ws As Worksheet For Each ws In Worksheets ws.Name = Trim(ws.Name) Next ws End Sub
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
I do believe you are a rocket scientist. That did it - thanks!
How did those sheet names get created with spaces in them like that? Did you use another macro?
If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks