I have a workbook with a summary worksheet as the first sheet. I need to find a way to average the same cell, let's say L2, on every sheet except for the first sheet. I'm constantly adding new sheets so it would be more efficient to reference L2 on all sheets except "Summary" instead of adjusting it each time I add a sheet.
Thanks for your help
Last edited by trickydigger; 01-28-2011 at 05:35 PM. Reason: Solved
Do a search for 3D Cell Referencing.
Essentially it looks like this:
=Average(Sheet1:Sheet4!A1)
All sheets located between 1 and 4 are considered in the average function.
One thing I like to do is to create two blank templates and place them before and after the live data sheets. I name these sheets "Start" and "End". That way I can use the formula "=Average(Start:End!A1)" and it will always work as long as the added sheets are inserted before "End".
Please see the attached for an example.
Last edited by Whizbang; 01-27-2011 at 02:13 PM.
I'm using the following function to return the name of the last worksheet:
Is there a way for me to use this to avoid using the two blank templates?
Please Login or Register to view this content.
I tried this:
=AVERAGE(1.24:LastSheetName()!A18)
where 1.24 is the name of the first sheet to be averaged, and it returned an error.
Last edited by NBVC; 01-27-2011 at 04:31 PM.
Because that function is returning a string you need to use indirect.
=Average(Indirect("1.24:" & LastSheetName() & "!A18")
A note on Indirect. Indirect is very inefficient. Because it does not know which cell/sheet you are referencing, it recalculates every time the sheet calculates. This can cause your sheet to slow down if used a lot. So, less is more.
I tried using that and it autocorrected to the following:
=AVERAGE(INDIRECT("1.24:"&LastSheetName()&"!A18"))
Then it returned a REF error. What am I doing wrong?
I am having trouble getting Indirect to work with the variable sheet name as well. I'll keep working on it.
I just wanted to mention that I hide the two blank templates (Format -> Sheet -> Hide) so that, to all appearances, only the live data sheets exist. This helps prevent accidental editing on the template sheets. Does this sound acceptable or do you still wish for the solution to the indirect problem?
Your solution would work except for the fact that the cell that I am averaging on the worksheets is a sum of data that I input throughout the day as it comes in. This throws the summary average off as the full day's data is complete so it only reflects the data I've entered. So I would really need to average the second sheet, 1.24, and the sheet created yesterday (as I create a new sheet with the date per day).
You could leave the current day's sheet outside of the start/end sheets until it is completed, and then move it inside.
Attached is an example of what I am talking about.
All you need to do is move the Current Day sheet in from of Day1 and it will then be averaged.
That would work, but then it throws off the references that I have that point to data on the last sheet.
According to this site:
http://en.allexperts.com/q/Excel-105...ce-formula.htm
Indirect does not work with 3D referencing. Bummer. That was my bad for recommending that.
The only other solution is to use macros. You are already using VBA to create the LastSheetName function, so I don't imagine this will be a problem.
You would use this function like this:Please Login or Register to view this content.
=AvgM("A1")
Thanks Whiz! That works perfectly, but is there a way to change the code to have it ignore the last sheet? I'm running into the problem where it's throwing the averages off because I haven't entered in all the data for the day, and your first solution would work if I didn't have other references to the last sheet.
I really appreciate all the help! I wish I could give you Rep!
What I often do to help my macros and other formulas is to create a sheet called "System" and I populate this sheet with various data, either dynamic or static, that is used behind the scenes. I then hide this sheet so that it isn't in the way.
I recommend you creating a System sheet and putting a fromula in one of the cells that automatically generates the sheet name for the current day's sheet. Something like "=TEXT(TODAY(),"mm.dd")". Then name this cell (http://www.contextures.com/xlnames01.html). Then adapt the code above to include a test for the current sheet.
Something like this:
Replace "CurrentSheet" with whatever name you gave the cell in the System sheet.Please Login or Register to view this content.
If you find yourself needing to not include more and more sheets, for whatever reason, you can expand the named cellin the System tab from a single sheet name to a list of names. Then instead of "If Sht.Name <> "Summary" and Sht.Name <> Range("CurrentSheet") Then" you could do a "If IsError(Application.Match(Sht.Name,Range("DoNotAverage"),0) Then"
As far as rep, just take a few moments to read other threads. If you see anyone who posted helpful advice, even if it doesn't directly benefit yourself, give them rep. Then you can return here and give me more rep. Everyone loves to receive rep, but unfortunately not everyone loves to give it. There are a bunch of really helpful people on this site that dedicate their time and knowledge to helping complete strangers and they deserve thanks, even if they did not help you directly.
Last edited by Whizbang; 01-28-2011 at 09:21 AM.
This works perfectly, thank you!
How would you modify the code to add a cell across sheets instead of average (not adding the summary and last page as well)?
And I'm spreading the rep love
To do a sum instead of average, simply get rid of anything with "ShtCount".
Please Login or Register to view this content.
Works perfectly. Thank you so much. You really helped me out!
Last edited by trickydigger; 01-28-2011 at 05:41 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks