 |
 | 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 / New Users / November 2006summarizing worksheet data
Hi, I want to create what should be a relatively simple spreadsheet with, say, fifty worksheets in the workbook. Each worksheet would be identical in terms of number of rows & columns, as well as the type of data those columns contain. On the first worksheet, however, I'd like to be able to summarize the cumulative totals of all the other sheets. So, for instance, if cell a20 on worksheets 2 through 50 contained a formula that calculated a sum of the figures in a1:a19, I'd like to put a formula in a cell in the first worksheet that will total the cell a20 in all the other worksheets. Is there a simple way to do this? Thanks in advance, Jim  Signature"The trouble with the world is that the stupid are cocksure and the intelligent are full of doubt." --Bertrand Russell > Hi, > [quoted text clipped - 16 lines] > "The trouble with the world is that the stupid are cocksure and the > intelligent are full of doubt." --Bertrand Russell Hi Jim, See Excel Help "Refer to the same cell or range on multiple sheets" Ken Johnson =SUM(Sheet2:Sheet50!A20) > Hi, > [quoted text clipped - 16 lines] > "The trouble with the world is that the stupid are cocksure and the > intelligent are full of doubt." --Bertrand Russell There is a shortcut for entering this: =SUM('*'!A20) where the '*' automatically converts to all sheets other than the activate one. Excel 2002 seems to have introduced a bug where this can crash the application. Does anyone else have the same problem? > =SUM(Sheet2:Sheet50!A20) > [quoted text clipped - 18 lines] > > "The trouble with the world is that the stupid are cocksure and the > > intelligent are full of doubt." --Bertrand Russell It worked ok in xl2003 for my simple test. (I had never seen this before!) > There is a shortcut for entering this: > [quoted text clipped - 26 lines] > > > "The trouble with the world is that the stupid are cocksure and the > > > intelligent are full of doubt." --Bertrand Russell SignatureDave Peterson I have found it can crash on 2002 if I first omit the single quotes and add then add them after. I came across it by accident and haven't seen it documented - think it probably goes back to XL5 days and used to work fine in 97. It can be useful for referencing specific sheets in large workbooks e.g. '*abc*'! evaluates to all sheets containing "abc". > It worked ok in xl2003 for my simple test. > [quoted text clipped - 30 lines] > > > > "The trouble with the world is that the stupid are cocksure and the > > > > intelligent are full of doubt." --Bertrand Russell I have found it can crash on 2002 if I first omit the single quotes and add then add them after. I came across it by accident and haven't seen it documented - think it probably goes back to XL5 days and used to work fine in 97. It can be useful for referencing specific sheets in large workbooks e.g. '*abc*'! evaluates to all sheets containing "abc". > It worked ok in xl2003 for my simple test. > [quoted text clipped - 30 lines] > > > > "The trouble with the world is that the stupid are cocksure and the > > > > intelligent are full of doubt." --Bertrand Russell You're right Lori. I crashed my XL02 by being a little lazy. Copied a working formula to another cell: =SUM(Sheet2:Sheet5!F1) Selected <<Sheet2:Sheet5>> in the formula bar, And typed << '*' >> As I typed the last single quote, XL crashed! I *didn't* even have time to hit <Enter> before it happened ! ! ! After recovering the WB, entering the formula from scratch worked perfectly fine. When Windows asked for the error report to be sent, I OK'd it. Got a message back that a fix existed. Was told to update Office. I update Windows religiously, can't remember when I last updated Office. After the Office update, tried the same formula revision again, and XL *STILL* crashed. Sending the error report this time generated *no* "able to fix" message. Haven't tested if revising the '*ABC*' type argument has the same (crash) results. Have you? SignatureRegards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- I have found it can crash on 2002 if I first omit the single quotes and add then add them after. I came across it by accident and haven't seen it documented - think it probably goes back to XL5 days and used to work fine in 97. It can be useful for referencing specific sheets in large workbooks e.g. '*abc*'! evaluates to all sheets containing "abc". Dave Peterson wrote: > It worked ok in xl2003 for my simple test. > [quoted text clipped - 40 lines] > > > > "The trouble with the world is that the stupid are cocksure and the > > > > intelligent are full of doubt." --Bertrand Russell Hey, that one is neat, too. (Not the crashing part <bg>) > I have found it can crash on 2002 if I first omit the single quotes and > add then add them after. [quoted text clipped - 42 lines] > > > > Dave Peterson SignatureDave Peterson So it can crash in 03 and probably 07 too? :( To be safe you could enter the formula as text (with leading apostrophe) and then evaluate using Text to Columns > Finish. Also =EVALUATE("sum('*'!A1)") as a defined name works - though stability is obviously questionable. :) > Hey, that one is neat, too. > [quoted text clipped - 46 lines] > > > > > > Dave Peterson It did not crash for me in xl2003. (Sorry about the confusion.) > So it can crash in 03 and probably 07 too? :( To be safe you could > enter the formula as text (with leading apostrophe) and then evaluate [quoted text clipped - 55 lines] > > > > Dave Peterson SignatureDave Peterson Works fine in XL2k! So it's just the one on the middle - XL02. SignatureRegards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > So it can crash in 03 and probably 07 too? :( To be safe you could > enter the formula as text (with leading apostrophe) and then evaluate [quoted text clipped - 61 lines] >> > > >> > > Dave Peterson That's good to hear. So there's only one caveat then - be very careful when using with XL2002! One further point: aside from stability, evaluating these expressions without using the formula bar can also add flexibility. e.g. Suppose you want to sum cells in all sheets beginning with a,b,c,... You could enter a,b,c,... down column A of the master sheet and in column B fill down formulas ="=sum('"&A1&"*'a1)" which could be evaluated by pasting values and then choosing Edit > Replace "=" with "=". Or to count all sheets with nonempty cells in a script you could use: ?evaluate("counta('[Book1]*'!a1)") Anyway we've drifted off Jim's original innocuous question far enough and I've run out of new material. Glad it lead to a fruitful discussion though. > Works fine in XL2k! > [quoted text clipped - 72 lines] > >> > > > >> > > Dave Peterson thanks to all for the input - it was very helpful. Jim > That's good to hear. So there's only one caveat then - be very careful > when using with XL2002! [quoted text clipped - 95 lines] > > >> > > > > >> > > Dave 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.