MrExcel Publishing
Your One Stop for Excel Tips & Solutions

For Dave Hawley regarding 15492.html


Posted by Kevin James on April 24, 2001 5:34 PM

Hi Dave,

First, I am very impressed with the depth of your knowledge. I'm one of those "jack of all trades, master of none." I've learned more in one day from you than I have in months of reading books. (High-5 to you!)

Regarding your reply at:
15428.html

I was intrigued. I'd never seen a reference written that way. It does come with it's caveats though.

I created 4 tabs: Total, A, B, C.
Total contains a single formula with the reference the way you wrote it.
Tabs A thru C contain the value 10 in cells A1 through A4. So the total is 120 (40 * 3).

Now create a 5th tab (D) the right of C, and put the same values in A1 to A4. Move "D" in-between either A/B or B/C. You'll notice that Total now includes the values from the D tab. (160)

Move D either before A or (again) after D, and the Total will revert back to the original. Now move C to the right of D, go back and check Total and you notice the formula has been altered to include only A & B. If now you move B to the right of C, the formula only references tab A. And there is no way to fix it short of redoing the formula.

To be safe, would you agree that the following is better to recommend

sum(a!a1:a4,b!a1:a4,c!a1:a4)

I know it more effort, but safety in numbers is us a crunchers have to lean on.

If you find my logic hard to follow (I've never been great with being concise), I'd be glad to send you a sample.


Posted by Anon on April 24, 2001 6:32 PM


Add two blank worksheets - one in front of the first sheet you want to include in the total and the other at the end (you can name them something like Start and End). Use these sheet names in your formula and make sure that new sheets are only added between the Start & End sheets. Your formula will then not be affected by adding and deleting sheets.

Posted by Kevin James on April 24, 2001 6:43 PM

Yes, thank you Anon. It will also work to just not move tabs around. My question was more "standard practices" rather than tutorial. :)

Posted by Anon on April 24, 2001 7:49 PM


I think having blank start and end sheets is "standard practice".
If you hard code each sheet as you suggest in your formula, you cannot avoid having to revise your formula when sheets are added or deleted.
Using blank start and end sheets avoids this and also you can do anything you like (move, add, delete) between the two blank sheets without having to change the formula.


Posted by Dave Hawley on April 24, 2001 9:35 PM


Hi Kevin


Firstly, Thankyou for the kind words!

Asa annon has suggested adding two blank worksheets would help. I myself would prefer to use a Custom function, like the one I have written below.

Function SumAllSheets(SumRange As Range)
'Written by OzGrid Business Applications
'www.ozgrid.com

''''''''''''''''''''''''''''''''''''''''''''
'Sum the chosen range on ALL sheets EXCEPT the _
Sheet that houses the formula.
'''''''''''''''''''''''''''''''''''''''''''''
Dim Wst As Worksheet
Dim SThisSheet As String
'forces a recalculation if a sheet is added
Application.Volatile
'Pass the activesheet name to a String
SThisSheet = ActiveSheet.Name

For Each Wst In ActiveWorkbook.Worksheets
'Sum the chosen range on all sheets Except _
'Sheet(SThisSheet)
If Wst.Name <> SThisSheet Then
SumAllSheets = WorksheetFunction.Sum _
(Wst.Range(SumRange.Address)) + SumAllSheets
End If

Next Wst


End Function


'To put this function in push Alt+F11 and then go to Insert>Module. Paste in the code above. Now push Alt+Q to return to Excel.

Select the cell on you "Total" sheet that you want the total to appear in. Now push Shift+F3 and scroll down to "User Defined Functions" then select "SumAllSheets". Use it as you would the normal sum function ie; select the range you want to sum (but only on the one sheet). You will end up with a simple formula like:

=SumAllSheets(A1:A4)


This will no automatically sum range A1:A4 on ALL sheets EXCEPT the one the formula is on. Each time you add a sheet now it will incorporate it. You may need to push F9 to force a recalculation.


Dave


OzGrid Business Applications

Posted by Kevin James on April 24, 2001 11:00 PM

Well, what can I say? If I were to attach a firewire to both our brains and try downloading all you're wealth of knowledge, we'd probably have a mess all over the walls -- my brains.

I took to BASIC back in the early 1980's like a duck in water. I don't know why, but for some reason Visual Basic makes some quantum leaps in logic that I find hard to follow. I've dabbled with it for years now, but to sit down and write from scratch, I just can't do.

On the flip side, I have amazed my peers with what I'm able to accomplish with formulas.

I have made notes on several postings you've made both regarding formulas and VBA. I plan on using them to further enhance my skills.

Thanks

Posted by Dave Hawley on April 25, 2001 12:37 AM


LOL! I have had my brains and dynamite mentioned in the same sentence before, but never in such a nice way :o)

I would say that, if you could get your head aound BASIC, VBA should be a walk in the park. Just forget everthing they taught you in BASIC :o)

I used to be able to achieve almost anything with Worksheet formulas, then I took to VBA and never looked back. I must admit though learning Excel from the bottom up helps me no end in my VBA coding. As I'm so familiar with the interface of Excel, I can, 9 times out of 10 avoid those painfully slooooowwwww Loops.


There is lots more tips, tricks and VBA code on my Website all free fo the taking as well!


Dave
OzGrid Business Applications

Posted by Anon on April 25, 2001 3:18 AM


I disagree with :-
"prefer to use a Custom function"

Not necessary to get involved with VBA when simple worksheet functions can do the job.