 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / November 2006Tip(s) from Excel User Conference
West Coast Excel User Conference October 25 2006 - October 27 2006 I know Bob U. and Jon P. were presenters. Did anyone else attend the conference? If yes, wound you mind sharing with the group one favourite tip that you took away. As a matter of fact, anyone who didn't go but has an urge to tell your latest "discovery," you are more than welcome. Look forward to a sharing spirit. The experts have been more than generous. Thank you! Have a good weekend! Epinn Actually, the 3 presenters were Bob U (me), Damon Longworth, and Mike Alexander (Jon P did not make it). The best tip I have is for you guys to buy my book (tips) called "This isn't Excel, it's Magic" (http://www.iil.com/iil/excelmagic). My tips & tricks presentation had tips from this book. But, since you ask, I'll share a tip I just learned today! Enter this in a cell: =SUM('*'!A1) (that's single quotes around the *) and then re-examine the formula. Sums all A1's from all sheets except the sheet it was entered on. Bob Umlas > West Coast Excel User Conference > October 25 2006 - October 27 2006 [quoted text clipped - 14 lines] > > Epinn Well, Bob, I tried that little =SUM('*'!A1) trick and this is what I see 1) On entering the formula on Sheet 1, Excel immediately converts the '*' to Sheet2:Sheet3 2) Excel immediately crashes Tried it twicee with identical results Using Win 2k and Office XP Pro What's your configuration? > Actually, the 3 presenters were Bob U (me), Damon Longworth, and Mike > Alexander (Jon P did not make it). The best tip I have is for you guys to buy [quoted text clipped - 25 lines] > > > > Epinn Win XP, Office XP SBE (all updates/patches applied) The first time I tried it worked OK. Sheet names: Sheet1, Sheet2, Sheet3. Formula entered on Sheet1. The second time I tried it: I deleted the formula I renamed Sheet2 ---> Sheet5 Re-entered the formula Excel crashed! Biff > Well, Bob, I tried that little =SUM('*'!A1) trick and this is what I see > [quoted text clipped - 43 lines] >> > >> > Epinn Lori posted that it crashes xl2002. But it works ok in xl2003. > Well, Bob, I tried that little =SUM('*'!A1) trick and this is what I see > [quoted text clipped - 37 lines] > > > > > > Epinn  SignatureDave Peterson And another question -- why weren't YOU there? :-) > West Coast Excel User Conference > October 25 2006 - October 27 2006 [quoted text clipped - 14 lines] > > Epinn Did you pick this up from our earlier thread today: http://groups.google.com/group/microsoft.public.excel/browse_frm/thread/566f3e9d dfb5a1da/c8c6c50bd920e56c?lnk=raot#c8c6c50bd920e56c or is it just coincidence? Happy to have found a trick many others are not aware of - not even Bob... Bob wrote: > And another question -- why weren't YOU there? > :-) [quoted text clipped - 17 lines] > > > > Epinn I shared it with others in another forum. Bob saw that shared post. > Did you pick this up from our earlier thread today: > [quoted text clipped - 25 lines] > > > > > > Epinn SignatureDave Peterson Thank you all for your contribution. Good timing as I have lately been learning how to add values across worksheets. This trick won't add the sheet that the formula is in. So, I place the formula sheet in between the sheets I want to add up. This is what I have discovered. Open a brand new workbook. Have the three sheets as default. Insert a blank new sheet before Sheet3. So, I have Sheet1, Sheet2, Sheet4 and Sheet3 (in this order). I enter the formula into a cell in Sheet4. =SUM('*'!A1) It is pretty smart that it adds up A1 in Sheet1,Sheet2 and Sheet3 even though they are not in sequence/contiguous. I get the correct result. Then I drag Sheet3 to after Sheet1 and end up with Sheet1,Sheet3,Sheet2 and Sheet4. Check the result again on Sheet4. Wrong! I have learned that to avoid potential future problem, I must place the formula/summary sheet either in the beginning or at the end of the sheets to be added up. Secondly, I must double check the formula when I drag the sheet(s) around. I have learned more than a new trick. Thanks again. Anyone else has something else to share? The more, the better. Epinn I shared it with others in another forum. Bob saw that shared post. Lori wrote: > Did you pick this up from our earlier thread today: > [quoted text clipped - 27 lines] > > > > > > Epinn SignatureDave Peterson I tested the wildcard tip by Lori and I say "neat!" Sheet names are: T1, nT234e, T555 Formula is = sum('T*'!A1) Only T1 and T555 are picked up. Thank you all for your contribution. Good timing as I have lately been learning how to add values across worksheets. This trick won't add the sheet that the formula is in. So, I place the formula sheet in between the sheets I want to add up. This is what I have discovered. Open a brand new workbook. Have the three sheets as default. Insert a blank new sheet before Sheet3. So, I have Sheet1, Sheet2, Sheet4 and Sheet3 (in this order). I enter the formula into a cell in Sheet4. =SUM('*'!A1) It is pretty smart that it adds up A1 in Sheet1,Sheet2 and Sheet3 even though they are not in sequence/contiguous. I get the correct result. Then I drag Sheet3 to after Sheet1 and end up with Sheet1,Sheet3,Sheet2 and Sheet4. Check the result again on Sheet4. Wrong! I have learned that to avoid potential future problem, I must place the formula/summary sheet either in the beginning or at the end of the sheets to be added up. Secondly, I must double check the formula when I drag the sheet(s) around. I have learned more than a new trick. Thanks again. Anyone else has something else to share? The more, the better. Epinn "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message news:454BD629.E1BC1902@verizonXSPAM.net... I shared it with others in another forum. Bob saw that shared post. Lori wrote: > Did you pick this up from our earlier thread today: > [quoted text clipped - 27 lines] > > > > > > Epinn SignatureDave Peterson After you hit enter, excel expanded this: =SUM('*'!A1) to: =SUM(Sheet1:Sheet2!A1,Sheet3!A1) After you drag sheet3 after sheet1, your formula is still: =SUM(Sheet1:Sheet2!A1,Sheet3!A1) Which means to add all the values in A1 in all the sheets between sheet1 and sheet2 (including sheet3 since in that "sandwich" of sheets), then add Sheet3's A1 one more time. It may not be what you expect, but the formula evaluates to the correct (but not your intended) sum. It's not too unlike this in B1: =sum(a1:a5,a3) It adds up just fine, but it may not be what you wanted. <<snipped>> > It is pretty smart that it adds up A1 in Sheet1,Sheet2 and Sheet3 even though they are not in sequence/contiguous. I get the correct result. Then I drag Sheet3 to after Sheet1 and end up with Sheet1,Sheet3,Sheet2 and Sheet4. Check the result again on Sheet4. > > Wrong! <<snipped>> SignatureDave Peterson Yes, I was aware of the logic behind the "wrong" result before I posted. When we drag down a cell containing a formula, the relative address is changed. I think this is different and there is no way that Excel knows how to readjust. ;) Next time, I'll say it is not the result that I have expected but ...... Epinn After you hit enter, excel expanded this: =SUM('*'!A1) to: =SUM(Sheet1:Sheet2!A1,Sheet3!A1) After you drag sheet3 after sheet1, your formula is still: =SUM(Sheet1:Sheet2!A1,Sheet3!A1) Which means to add all the values in A1 in all the sheets between sheet1 and sheet2 (including sheet3 since in that "sandwich" of sheets), then add Sheet3's A1 one more time. It may not be what you expect, but the formula evaluates to the correct (but not your intended) sum. It's not too unlike this in B1: =sum(a1:a5,a3) It adds up just fine, but it may not be what you wanted. Epinn wrote: <<snipped>> > It is pretty smart that it adds up A1 in Sheet1,Sheet2 and Sheet3 even though they are not in sequence/contiguous. I get the correct result. Then I drag Sheet3 to after Sheet1 and end up with Sheet1,Sheet3,Sheet2 and Sheet4. Check the result again on Sheet4. > > Wrong! <<snipped>> SignatureDave Peterson This type of "sandwich" can be useful. I like to have two worksheets named Start and End surrounding the worksheets that I want to add (say). Then I can do: =sum(start:end!a1) And I can move worksheets into that sandwich or move worksheets out of the sandwich. It makes playing what-if games pretty nice. > Yes, I was aware of the logic behind the "wrong" result before I posted. > [quoted text clipped - 35 lines] > > Dave Peterson SignatureDave Peterson Yes, totally agree. I only learned about this a couple of days ago. Do you usually hide the dummy sheets? Thank you for sharing. Epinn This type of "sandwich" can be useful. I like to have two worksheets named Start and End surrounding the worksheets that I want to add (say). Then I can do: =sum(start:end!a1) And I can move worksheets into that sandwich or move worksheets out of the sandwich. It makes playing what-if games pretty nice. Epinn wrote: > Yes, I was aware of the logic behind the "wrong" result before I posted. > [quoted text clipped - 35 lines] > > Dave Peterson SignatureDave Peterson I don't. But I know many like to. If I hide those sheets, then it gets less obvious what's happening. I will lock all the cells and protect the sheet, though. I don't want someone (me!) changing a cell that affects my results. > Yes, totally agree. I only learned about this a couple of days ago. Do you usually hide the dummy sheets? > [quoted text clipped - 58 lines] > > Dave Peterson SignatureDave Peterson Dave, Good idea. I think I'll do the same. Does anyone want to know what I have learned today? Bob U. mentioned that Mike Alexander was one of the presenters. I found out from his web site the function that was used to calculate age. I have always liked DATEDIF but Mike used one function that I didn't even know exists. DAYS360( ) <--- I haven't come across this function on this board. =IF(D8="","",DAYS360(D8,D2)/360) (Format>number decimal places = 0) D8 = date of birth D2 = today's date. DAYS360 is based on a 30-day month for 12 months and leap year February is within 30 days. Any comments on DAYS360( )? Epinn I don't. But I know many like to. If I hide those sheets, then it gets less obvious what's happening. I will lock all the cells and protect the sheet, though. I don't want someone (me!) changing a cell that affects my results. Epinn wrote: > Yes, totally agree. I only learned about this a couple of days ago. Do you usually hide the dummy sheets? > [quoted text clipped - 58 lines] > > Dave Peterson SignatureDave Peterson |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.