add-in combo box date excel 2000 excel 2002 excel 97 excel XP format formatting formula grouping loan - amortization macros subtotal sum excel tutorial workbook worksheet vba
| Article | Body |
| 232 | This happens when your list has many formulas. There's an article in the MSKB that explains: XL2000: Excel AutoFilter Status Bar Message Shows "Filter Mode" Q213886) /default.aspx?scid=kb;EN-US;q213886 or XL: AutoFilter Status Bar Message Shows "Filter Mode" (Q189479) /default.aspx?scid=kb;en-us;Q189479 A code solution: XL: How to Count Rows Displayed After Data Has Been Filtered (Q148621) /default.aspx?scid=kb;en-us;Q148621 ***A workaround that I sometimes use: If you want to see a record count for the visible rows which contain data, you can use the Subtotal function in a formula in the same row as your headings. For example, if you want to count the visible entries in column C which contain numbers, you could use this formula: =SUBTOTAL(2,C:C) The 2 in the first argument tells Excel to use the COUNT function on the visible cells in the range. If you want to count rows that contain text, you could change the formula: =SUBTOTAL(3,D:D)-1 The 3 is for the COUNTA function, and the -1 removes one for the row which contains the column heading. NOTE: Blank cells will not be counted -- use a column with no blank cells. Celeste wrote: Sometimes the number of records are displayed in the lower left hand corner of screen, and sometimes they are not. I know if I right click in that location, another drop down menu appears. One can select "count nums", "sum", etc. It doesn't seem to make any difference which is selected. I really use the number of records displayed by the autofilter selection but I can't get it to do it every time. Lately, it hasn't been working at all. It doesn't seem to necessarily be related to free RAM or file size or even the number of records included in the autofilter. If anyone knows how this works, please pass it on to me! Thanks! Grand Junction, CO -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 233 | Thanks, I'll check these out. Typically, I do have lots of formulae. -----Original Message----- This happens when your list has many formulas. There's an article in the MSKB that explains: XL2000: Excel AutoFilter Status Bar Message Shows "Filter Mode" Q213886) /default.aspx?scid=kb;EN- US;q213886 or XL: AutoFilter Status Bar Message Shows "Filter Mode" (Q189479) /default.aspx?scid=kb;en- us;Q189479 A code solution: XL: How to Count Rows Displayed After Data Has Been Filtered (Q148621) /default.aspx?scid=kb;en- us;Q148621 ***A workaround that I sometimes use: If you want to see a record count for the visible rows which contain data, you can use the Subtotal function in a formula in the same row as your headings. For example, if you want to count the visible entries in column C which contain numbers, you could use this formula: =SUBTOTAL(2,C:C) The 2 in the first argument tells Excel to use the COUNT function on the visible cells in the range. If you want to count rows that contain text, you could change the formula: =SUBTOTAL(3,D:D)-1 The 3 is for the COUNTA function, and the -1 removes one for the row which contains the column heading. NOTE: Blank cells will not be counted -- use a column with no blank cells. Celeste wrote: Sometimes the number of records are displayed in the lower left hand corner of screen, and sometimes they are not. I know if I right click in that location, another drop down menu appears. One can select "count nums", "sum", etc. It doesn't seem to make any difference which is selected. I really use the number of records displayed by the autofilter selection but I can't get it to do it every time. Lately, it hasn't been working at all. It doesn't seem to necessarily be related to free RAM or file size or even the number of records included in the autofilter. If anyone knows how this works, please pass it on to me! Thanks! Grand Junction, CO -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html . |
| 813 | May I take the opportunity to add my 2 cents worth? I started with Excel in Excel 95. I learned maybe 60% of what I know about Excel from the Help files, and the rest from these newsgroups. I now have Excel 2002. And I am hugely disappointed with the change in the Help files in both Word and Excel. If N. Squire (the OP) happens to be using Excel 2002 and went searching in Help, as you might, for "horizontal scroll bar" he would have been faced with 20 possible pages of information. On my system, the first few are: "Insert subtotals", "Include different data in an offline cube", "Troubleshoot security and protection". None of the 20, from what I can see, mentions the horizontal scroll bar on the side of a worksheet or mentions Tools | Options etc. No wonder he can't find what he needs! I used to feel confident that I could tell people that they could find the information they needed in the Help files. I'm no longer confident enough to say that and send them, instead, to the nearest bookshop in search of John Walkenbach's books. With due respect, thanks, praise and genuflection, such books should be an adjunct to, not a replacement for, Help files that are structured such that users can find what they need. Maybe it's just me. But I find the new Help file system imponderable. Perhaps those of you who have Microsoft's collective ear could point out that this is not supporting the company's publicly-stated corporate policy of being driven by the market and making it easy for the user. Anne "Harald Staff" <harald.staff@eunet.no wrote in message news:u6i6$MGHCHA.2604@tkmsftngp11... Hi John Some folks must fight (or worse; persuade) the paperclip to be allowed to enter Help. It is very exhausting, especially on international systems. So I'd prefer a MVP or a tech writer answering <g. Best wishes Harald "John Walkenbach" <john@j-walk.com wrote in message news:epCpV3FHCHA.2652@tkmsftngp11... FYI, answers to these types of questions are readily available in Excel's Help system. You might find that approach more efficient than posting to a newsgroup. |
| 1471 | You could have XL automatically enter Jason's formula for you by simply selecting the cell under the last cell in the column you wish to total (B5) and then Double Click on the Sigma (AutoSum) icon in the formula bar. HTH RD "Jason Morin" <jason.morin@us.exel.com wrote in message news:1335001c22368$6acfcf10$a4e62ecf@tkmsftngxa06... Use SUBTOTAL when filtering: =SUBTOTAL(9,B2:B4) Happy 4th! HTH Jason Atlanta, GA -----Original Message----- Take a simple excel array as below :- name jan feb mar steve 4 4 5 paul 3 3 1 steve 6 4 2 totals 13 11 8 If you filter on name and then select steve you get :- name jan feb mar steve 4 4 5 steve 6 4 2 13 11 8 Fine but the totals are wrong - how can you filter and get :- name jan feb mar steve 4 4 5 steve 6 4 2 10 8 7 Thanks, Andy. . |
| 2683 | Hi If the rows are hidden in a filtering process, you can use the SUBTOTAL() function. If they are hidden manually, which seems to be the situation here, please see my answers in microsoft.public.excel.worksheet.functions from the 14-7 "Re:Average and Median of visible cells" -- Best regards Leo Heuser MVP "S. Peter" <speter@hotmail.com skrev i en meddelelse news:#EM88DwLCHA.2748@tkmsftngp13... Hi Are there any function to sum only visible cell? My function is sum(A1:A100) but sometime I hide row 11 to 20, sometime I hide row 21 to 30 I would like to sum the value in visible cell. Thank you for your help |
| 3007 | Works fine for me Bruce, what kind of error do you get? Btw, what you do is more of a countif than a sumif... Autofilter would work nicely for these kinds of operations as well, from dropdown in Salary select custom greater than 4000 AND less than 6001, select 3 in Option and B in SavingsOption, now use a formula like =SUBTOTAL(3,Salary) Regardless, you formula works for me (as expected).. -- Regards, Peo Sjoblom "Bruce" <bruce@nha.co.za wrote in message news:0f2c01c232ff$aba93e80$2ae2c90a@phx.gbl... Hi I have the following array formula which works without any problems. =SUM((Salary4000)*(Salary<6001)*(Option=3)) I now want to add one more criteria to this sum, and that it where SavingsOption="B", but when I do this I get an error. =SUM((Salary4000)*(Salary<6001)*(Option=3)* (SavingsOption="B")) I have checked the range names and they are fine. I am using CTRL SHIFT and ENTER so that's not the problem either. Any ideas how I could get this to work? I will more than likely have to do a sum with 5 criteria - is there a scalable solution I can use? Any help wpuld be much appreciated. thanks Bruce |
| 3038 | Hi Peo I get a #N/A error. It's very odd that it doesn't work for me but does for you. I have tried it again and still no luck. Thanks for the autofilter suggestion, but I am trying to do this all in hidden worksheet which then generates graphs so I don't think that will really work. thanks anyway! -----Original Message----- Works fine for me Bruce, what kind of error do you get? Btw, what you do is more of a countif than a sumif... Autofilter would work nicely for these kinds of operations as well, from dropdown in Salary select custom greater than 4000 AND less than 6001, select 3 in Option and B in SavingsOption, now use a formula like =SUBTOTAL(3,Salary) Regardless, you formula works for me (as expected).. -- Regards, Peo Sjoblom "Bruce" <bruce@nha.co.za wrote in message news:0f2c01c232ff$aba93e80$2ae2c90a@phx.gbl... Hi I have the following array formula which works without any problems. =SUM((Salary4000)*(Salary<6001)*(Option=3)) I now want to add one more criteria to this sum, and that it where SavingsOption="B", but when I do this I get an error. =SUM((Salary4000)*(Salary<6001)*(Option=3)* (SavingsOption="B")) I have checked the range names and they are fine. I am using CTRL SHIFT and ENTER so that's not the problem either. Any ideas how I could get this to work? I will more than likely have to do a sum with 5 criteria - is there a scalable solution I can use? Any help wpuld be much appreciated. thanks Bruce . |
| 3042 | Bruce, I suspect that the ranges are not of equal size.. If Salary is A2:A200, then SavingsOption can't be for instance C2:C199. If so you'll get the #N/A error, so check you defined names to see if the ranges are of equal size.. -- Regards, Peo Sjoblom "Bruce" <bruce@nha.co.za wrote in message news:085501c2331c$dc06f6e0$37ef2ecf@TKMSFTNGXA13... Hi Peo I get a #N/A error. It's very odd that it doesn't work for me but does for you. I have tried it again and still no luck. Thanks for the autofilter suggestion, but I am trying to do this all in hidden worksheet which then generates graphs so I don't think that will really work. thanks anyway! -----Original Message----- Works fine for me Bruce, what kind of error do you get? Btw, what you do is more of a countif than a sumif... Autofilter would work nicely for these kinds of operations as well, from dropdown in Salary select custom greater than 4000 AND less than 6001, select 3 in Option and B in SavingsOption, now use a formula like =SUBTOTAL(3,Salary) Regardless, you formula works for me (as expected).. -- Regards, Peo Sjoblom "Bruce" <bruce@nha.co.za wrote in message news:0f2c01c232ff$aba93e80$2ae2c90a@phx.gbl... Hi I have the following array formula which works without any problems. =SUM((Salary4000)*(Salary<6001)*(Option=3)) I now want to add one more criteria to this sum, and that it where SavingsOption="B", but when I do this I get an error. =SUM((Salary4000)*(Salary<6001)*(Option=3)* (SavingsOption="B")) I have checked the range names and they are fine. I am using CTRL SHIFT and ENTER so that's not the problem either. Any ideas how I could get this to work? I will more than likely have to do a sum with 5 criteria - is there a scalable solution I can use? Any help wpuld be much appreciated. thanks Bruce . |
| 3227 | I took your scenario and compiled a list of unique names using an advanced filter. and used a counta statement to get the number of names in this list and below the list of dates I useda subtotal(5,range) to get the minimum date of the visible range. I then ran the following macro Application.ScreenUpdating = False Dim Uniqueno As Integer Range("A1:C20").Select Selection.AutoFilter Range("a25").Select Uniqueno = ActiveCell ActiveCell.Offset(1, 0).Select For i = 0 To Uniqueno Selection.AutoFilter Field:=3, Criteria1:=ActiveCell Range("a23").Select Selection.Copy Range("a26").Select ActiveCell.Offset(i, 1).Range("A1").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False ActiveSheet.ShowAllData ActiveCell.Offset(1, -1).Range("a1").Select Next i Application.ScreenUpdating = True End Sub where a1:c20 was my range, a26 was the first unique name in my filter list, a25 held the counta statement and a23 the subtotal statement. No doubt the macro could be improved by removing the selects. Alan -----Original Message----- I have a list of names (about 4000) with many duplicates. Many, but not all, of the names have dates associated with them. How can I determine the earliest date that is associated with each name? If a name has no associated date, I would like to know that as well. I know I could sort the list by dates and remove duplicates, but I'm hoping there is another solution with a function(s). It seems if there were a MINIF function, I could address my problem, but there is not. Is there a way I can solve the problem using a combination of existing functions, or a macro? -- Ray Wright raycyn.wright@prodigy.com To reply, change "com" to "net" . |
| 3399 | t, Your check box should have a "cell link" property" which you need to set to some cell (which you can hide). This cell will indicate TRUE or FALSE as a function of the box. Now use that in your formula. If the checkbox is linked to D1, it might look something like this: =IF(D1, A2 * 33%, A2 * 12%) THis is equivalent to: =IF(D1 = TRUE, A2 * 33%, A2 * 12%) Regards from Virginia Beach, EarlK ------------------------------------------------------------- "tblanco" <blanco@cox.net wrote in message news:1a8901c235c0$c1f99ef0$9be62ecf@tkmsftngxa03... hi. i'm going nuts trying to figure out how to do this. i'm making a form in which i want to be user-friendly so when the checkbox is marked, it will know to calculate the fringe rate @ 33% for full-time and 12% for part-time in the subtotal... please let me know if you can figure this out for me! Thx so much in advance. |
| 3651 | Maybe you can use a workaround, for instance to count the values in A2:A20 that are greater than F1 and is less than or equal to F2 you can use this formula =SUMPRODUCT(($A$2:$A$20F1)*($A$2:$A$20<=F2)*(SUBTOTAL(3,OFFSET($A$2,ROW($A$ 2:$A$20)-MIN(ROW($A$2:$A$20)),,1)))) the above will work using the autofilter and only count visible cells -- Regards, Peo Sjoblom "Daniel Schmitz" <schmitzd@epost.de wrote in message news:ai76tm$1b1$1@nets3.rz.RWTH-Aachen.DE... Hello! I have some datarecords in Excel and want to have a propability distribution using the FREQUENCY function. This also works quite good. If I use an autofilter on this data the according cells are hidden. But in the calculation with the FREQUENCY function all datarecords are still included. Is there a possibility to include just the non-hidden cells in the analysis? Thanks for any advice! Daniel |
| 3928 | Hi Deb Far be it from me to question you <vbg, but I tried it on XL2002 before posting, and without anything in the data area, I achieved a list of Employees under Position under Section Head under Group which I thought was what the OP requested. I can send you my sample WB if you want<gdr Cheers -- Regards, Roger Govier Technology 4 U "Debra Dalgleish" <dsd@contextures.com wrote in message news:3D4BB25C.7020602@contextures.com... Hi Roger, You can't leave the Data area of the Pivot Table empty, so you'd have to drag one of the fields into there. Put all 4 into the row area, as you suggest, then drag another copy of the EmpName field button into the data area, where it becomes Count of EmpName. Deb Roger Govier wrote: Hi Nimish You can't get text to appear in the data, only Counts, Sums etc. If you drag items to the Row area in the order Section Head Group Position Emp Name Don't bother to add anything to the data area or row area. Double click on the field heading in turn and click None for Subtotals, then I think you will get the layout you are looking for. -- Regards, Roger Govier Technology 4 U "Nimish" <parikhnd@yahoo.com wrote in message news:66e7948e.0208022252.1739e691@posting.google.com... Hello, I want to place text string in data field of pivot table. When I drag a text field in data area of pivot table I get data is "Count of that field". What I want is actual text in data field rather than count. Here is data structure - EmpName Group SectionHead Position Report should be like - Rows side shows Position Columns Side Shows First SectionHead and then Group Data area offers trouble. In data area I want to have text string contained in EmpName. Instead I am left with option of "Count of EmpName" Any Help, Nimish -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 3929 | Hi Deb Just tested it under XL2K, and in that case you are absolutely correct, you do need to drag something into Data area and have a Count. My apologies. It is obviously an XL2002 new feature. I should have tested in under XL2K as well (or not listened to your advice and moved on the XL2002<g) If the OP doesn't want the count of number to appear, as it sounded more like he wanted to have just an organisational chart, then he could always right click on the Pivot Table, choose Table Options and remove Grand Totals for Rows and Grand Total for columns. The Total column at the right of his table could be suppressed by formatting the text as White (or the same colour as the background) -- Regards, Roger Govier Technology 4 U "Roger Govier" <roger@technology4u.co.uk wrote in message news:u74nwstOCHA.2368@tkmsftngp10... Hi Deb Far be it from me to question you <vbg, but I tried it on XL2002 before posting, and without anything in the data area, I achieved a list of Employees under Position under Section Head under Group which I thought was what the OP requested. I can send you my sample WB if you want<gdr Cheers -- Regards, Roger Govier Technology 4 U "Debra Dalgleish" <dsd@contextures.com wrote in message news:3D4BB25C.7020602@contextures.com... Hi Roger, You can't leave the Data area of the Pivot Table empty, so you'd have to drag one of the fields into there. Put all 4 into the row area, as you suggest, then drag another copy of the EmpName field button into the data area, where it becomes Count of EmpName. Deb Roger Govier wrote: Hi Nimish You can't get text to appear in the data, only Counts, Sums etc. If you drag items to the Row area in the order Section Head Group Position Emp Name Don't bother to add anything to the data area or row area. Double click on the field heading in turn and click None for Subtotals, then I think you will get the layout you are looking for. -- Regards, Roger Govier Technology 4 U "Nimish" <parikhnd@yahoo.com wrote in message news:66e7948e.0208022252.1739e691@posting.google.com... Hello, I want to place text string in data field of pivot table. When I drag a text field in data area of pivot table I get data is "Count of that field". What I want is actual text in data field rather than count. Here is data structure - EmpName Group SectionHead Position Report should be like - Rows side shows Position Columns Side Shows First SectionHead and then Group Data area offers trouble. In data area I want to have text string contained in EmpName. Instead I am left with option of "Count of EmpName" Any Help, Nimish -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 3930 | Don't get smart with me, Roger. It's much earlier here and I haven't finished my coffee. What I *should* have said was..."In the Pivot Table Wizard, you can't leave the Data Area empty"....etc. I guess some people do the layout on the worksheet, so it's not a problem, but I always start with a layout from the Wizard, and fine tune it on the worksheet. Old habits die hard, eh? Deb Roger Govier wrote: Hi Deb Far be it from me to question you <vbg, but I tried it on XL2002 before posting, and without anything in the data area, I achieved a list of Employees under Position under Section Head under Group which I thought was what the OP requested. I can send you my sample WB if you want<gdr Cheers -- Regards, Roger Govier Technology 4 U "Debra Dalgleish" <dsd@contextures.com wrote in message news:3D4BB25C.7020602@contextures.com... Hi Roger, You can't leave the Data area of the Pivot Table empty, so you'd have to drag one of the fields into there. Put all 4 into the row area, as you suggest, then drag another copy of the EmpName field button into the data area, where it becomes Count of EmpName. Deb Roger Govier wrote: Hi Nimish You can't get text to appear in the data, only Counts, Sums etc. If you drag items to the Row area in the order Section Head Group Position Emp Name Don't bother to add anything to the data area or row area. Double click on the field heading in turn and click None for Subtotals, then I think you will get the layout you are looking for. -- Regards, Roger Govier Technology 4 U "Nimish" <parikhnd@yahoo.com wrote in message news:66e7948e.0208022252.1739e691@posting.google.com... Hello, I want to place text string in data field of pivot table. When I drag a text field in data area of pivot table I get data is "Count of that field". What I want is actual text in data field rather than count. Here is data structure - EmpName Group SectionHead Position Report should be like - Rows side shows Position Columns Side Shows First SectionHead and then Group Data area offers trouble. In data area I want to have text string contained in EmpName. Instead I am left with option of "Count of EmpName" Any Help, Nimish -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 3955 | Thanks for the reply Debra, I downloaded and tried your sample file, the idea is almost there. On your workbook l would need subtotals for each "Categoryname" eg beverages, seafood etc When l tried to copy over your code into my wookbook and modified the range names and created "Critera" and "Extract", l had mixed results. The best l could get was basically copying all the data across, this was after many runtime errors (1004 and incorrect range names or ranges). My experience with VB is very limited. I think l need to look at it with a clear head and try again. Cheers Debra Fran "Debra Dalgleish" <dsd@contextures.com wrote in message news:3D497DD2.7050507@contextures.com... On my web site, I have a sample file that uses an Advanced Filter to extract records by week number. A macro places the records from the database onto a summary sheet. /excelfiles.html Look for the heading 'Product List by Week Number' and download the file named 'ProductsListWeekNum.xls ' |
| 4045 | This is posting 9 of Frequently Asked Questions for the Excel newsgroups microsoft.public.excel.misc, microsoft.public.excel.programming and microsoft.public.excel.worksheet.functions. Topics are: The Excel application and Excel files Worksheet functions and formats Dates and times Macros, VBA functions I didn't find my answer here, now what ? Good resources on the web Collected by Harald Staff, Microsoft Excel MVP. This FAQ can also be found at Debra Dalgleish's website /xlfaqIndex.html -it's worth a bookmark. If you reply to this posting, reply to a single group and quote as little as possible. ************************************************* **** The Excel application and Excel files **** * When I start Excel, why do a million files open up automatically? Menu Tools Options General has an entry for "Startup directory", and all files there will be loaded when Excel starts. Alter or remove this entry. * When I start XL, I get the error message "Compile error in Hidden Module" An add-in with a programming error is bothering you. 1 Tools, Add-ins 2 note which are checked 3 uncheck all (but one) 4 restart XL 5 if no error, check the next one and repeat from step 4 Got the error? uncheck the one you last checked. If no luck, see if there's an .xla file in the startup directory (see previous tip). Scanner software typically place itself there, sometimes with code errors like the one in question. Not an add-in? It will be a hidden workbook then. 1 Try (in Excel) Windows, Unhide and write down which files are listed. 2 Unhide them. 3 Now go to the VBE (press Alt-F11). 4 On the left side there should be the project explorer. 5 Select the first project you unhid 6 From the menu choose Debug, compile 7 No Errors? select the next project listed, repeat 6. * Why do the column headers show numbers instead of letters? How do I change my column headings so they are back to alpha letters instead of numbers ? Tools / Options / General / Settings / R1C1 Reference Style (uncheck) * How can I change the color of the sheet tabs? Excel 10 (XP) is the only version that can color sheet tabs. * I want to lock in my Title Row and keep it there Visible while I scroll down to see the rest of my data. Any ideas? Assuming title row is 1, select A2 then WindowFreeze Panes. * I need more than 256 columns and/or 65536 Rows. Excel has no more. Quattro Pro v9 has 1 million rows and 18278 columns. * I just began to design an intricate spreadsheet and after over an hour got an error message in Excel - which then closed automatically. Unfortunately, I did not save my file. Excel did not automatically recover the file. Is there anything I can do to get it back? No. There should be an Autosave add-in in Tools Addin menu that you now might consider start using. (But then again, you may not want to overwrite an existing file with every little test you do in it, so be careful). Jan Karel Pieterse has an add-in Autosafe.zip downloadable from www.bmsltd.co.uk/mvp. See also 's /dmcritchie/excel/backup.htm for more on backup and recovery. Finally, Excel XP has great backup and recovery tools, so upgrading is a good future solution to those problems. * All of a sudden a number of my Excel 2000 files have become "read-only". Clear out c:\windows\temp directory on the machine that houses the files, reboot. * I have an excel file that I use every day at work. Some time ago, mysteriously, the file began opening two copies of itself every time I double-click the icon. If I close one of the copies, both close. Any changes made to one copy show up in the other. This sounds like you just have two windows open that are displaying the same workbook. You can close the extra window by clicking the lower of the two X buttons in the upper right corner of the screen (don't click the top X button, as that will close Excel). Then save the workbook. * When I type a number; example 44 it becomes 0.44 even if I format the cell without decimals. Goto Tools/Options/Edit and uncheck the fixed decimals checkbox. * When I click on an Excel file to open it, the file opens but an error message is displayed that says a file with that name is already open and that I can not open two files open with the same name. If you are sure it's not really happening -you may have Book1 open and then attempt to open a Book1 from another folder- then try re-register Excel. click startrun and "C:\Program Files\Microsoft Office\Office\Excel.Exe" /regserver (include the quotes) adapt to fit your path. Other things to look at: Tools=Option=General Tab, make sure "Ignore Other Applications" is not checked * Why does Excel say my file has links, when I know it doesn't? Links come in several flavors; linked formulas, defined names (Insert Name Define menu), objects (buttons and stuff) assigned to remote macros, ... You might find the FINDLINK.XLA program useful - you can get it from Stephen Bullen's web site: * Is there a way to allow the use of autofilter on a protected worksheet ? This needs a macro to run first: Sub Protect_keep_filter() With ActiveSheet .EnableAutoFilter = True .Protect DrawingObjects:=True, _ contents:=True, Scenarios:=True, UserInterfaceOnly:=True End With End Sub Note that the .enableautofilter has to be reset each time you open your file. (It's not persistent between closes.) * How many worksheets I can put in a workbook? It is not a limit per se, but of course there is a practical one which depends on the computer resources.. * How many Characters can be placed in a Cell? In Excel 97 and above, you can have up to 32K characters in a worksheet cell, but only the first 1000 or so can be displayed (the exact number depends on your font and display characteristics). * I've lost my password ! Yeah sure. Well, there are passwords and there are passwords. One is to open the file, one other is to unprotect the spreadsheet, still another is to unprotect the workbook and yet another one is to unprotect the macro code. File and VBA passwords can not be cracked by a "normal macro", workbook and worksheet passwords are fairly easy. A search for "excel password" at / will find both commercial and free solutions of varying quality and brutality. * Can anyone advice how to protect an Excel file (and associated code) from un-authorized copying and/or create time limited functionality ? There is no fool-proof method for this, and most solutions limit themselves to require macros enabled on opening. This said, creating your own system is a fun challenge allowing lots of "evil creativity". * When I try to open XL, it freezes and all I can do is reboot * Excel crashes on me regularly, what can I do * EXCEL caused an invalid page fault... * Illegal Operation Error when starting Excel To-Do List: Try opening Excel without any addins or hidden workbooks: Start, Run, "C:\Program Files\Microsoft Office\Office\Excel.exe" /Automation Note you may have to change the path. If your error disappears, there is either an add-in or a (hidden) workbook that is loaded upon XL's start, which bothers you. - Try locating the XLSTART directory, move everything from there. - In XL: Tools, Addins, note the ones checked and uncheck them one at the time, each time restarting XL - In XL, Tools, options, general. Check if a path is entered after "Alternate startup file location". Clear it. Another option is to open XL in Safe mode: "C:\Program Files\Microsoft Office\Office\Excel.exe" /Safe Also, you might try: "C:\Program Files\Microsoft Office\Office\Excel.exe" /Regserver Yet another possible problem is a corruption of your toolbar customisation file. Locate all files with extension .xlb and rename the extension(s) to something like .old .Now try and start XL again. Finally, this is what MS has to say: /default.aspx?scid=kb;en-us;Q280504 **** Worksheet functions and formats **** * How can I protect a formulas from being deleted or changed? Select all cells that users ARE allowed to change. Go menu Format Cells Protection and uncheck Locked. When done, protect the worksheet in menu Tools Protection Protect worksheet. * How can I enter the date into a cell so it doesn't change every day? Press Ctrl ; (that's holding Ctrl down while pressing semicolon.) Ctrl : will enter the time. * I have to enter the Expenses and to select from some criteria. That means, the user can only enter 'DHL', 'FEDEX', 'UPS'. In other word once the user move into the corresponding cell under expenses it will popup a combo box with the above 3 Companies and user will select one. Select the region you want to apply this to, then select menu item Data/Validation. In the Allow dropdown, select List. In the Source textbox, enter "DHL,FEDEX,UPS" (without the quotes) * Is there a way to create a formula that will do this type of function =IF(D25 DOES NOT EQUAL E25 THAN D25 FONT WILL TURN RED) ? Try Format=Conditional Formatting: Select D25. Click on FormatConditional Formatting. Set Condition 1 - Cell Value Is, not equal to, click on E25 on the worksheet. Click on Format, select the Font tab. Under Colour, choose red. OK, OK. * Is there a way to enter a formula that will round a value to the nearest increment of 5? =ROUND(A1/5,0)*5 * or to the nearest quarter ? =ROUND(A1/0.25,0)*0.25 * I am looking for a formula that will look at an adjacent column then find that value in another sheet and return the data from an adjacent column. Try VLOOKUP. =VLOOKUP(A2,Sheet2!A2:B100,2,FALSE) * I want to add the largest/smallest 5 entries in A column. =SUM(LARGE(A:A,{1,2,3,4,5})) * I have data stored in rows and I want to change these rows to columns Select the data, copy it, select where you want it, do editpaste special, check the transpose option, click OK * How do I pick 20 random items from a list of 100 ? Enter the items down A1:A100. In B1:B100 enter formula =RAND(). Sort the list by B column; top 20 rows is your selection. Press F9 for new B numbers and repeat for a new selection. * Is it possible to write a SUMIF worksheet formula to sum visible cells only? If the cells are hidden as a result of AutoFiltering, you can use the SUBTOTAL function, with 9 as the first argument * I can sum all numbers =10 with =SUMIF(A1:A20,"=10") . But how do I enter two criterias so I can sum numbers between 5 and 10 ? That equals sum of all =5 minus sum of all 10: =SUMIF(A1:A20,"=5")-SUMIF(A1:A20,"10") Or you can use this method: =SUMPRODUCT((A1:A20=5)*(A1:A20<=10)*A1:A20) * Using DataSubtotals, I would like to create a table that has just these subtotals, not the hidden detail rows. 1. Once you've applied the Subtotal, collapse the outline so that only the rows you want to copy are visible. 2. Select the cells. 3. Choose EditGo To, click the Special button 4. Select 'Visible Cells Only', click OK 5. Click the Copy button 6. Go to another sheet, and paste * When I use AutoFilter I don't see all the items in the drop down list. Why not? An AutoFilter dropdown list will only show 1000 entries. You could add a new column, and split the list into 2 or three groups, e.g.: =IF(LEFT(C2,1)<"N","A-M","N-Z") Filter on this column first, then by the intended criteria. Another option is to choose Custom from the drop-down list, and type the criteria. * In a cell I have "lastname, firstname". I want to put lastname in one cell and first name in another. Use DataText to columns and specify the comma as a delimiter. * How can I prevent hyperlinks from appearing when I type an email address? You can turn that option off in Excel XP only. All versions: Select the cell and press Ctrl+Z, this will convert the hyperlink back to text. The code below, when run on a selection, will also delete the hyperlinks. Sub delHyperlinks() Dim myCell As Range For Each myCell In Selection myCell.Hyperlinks.Delete Next myCell End Sub * When I import information from my merchant account the field which holds the 16 digit credit card information does not display it properly. Excel only maintains 15 significant digits for numbers and cannot be used for credit cards which require 16 digits. If you don't need to do math on them, input long numbers into a range that you've formatted as text. Both above -and also entries automatically converted to dates- can be prevented by entering a single ' before the actual entry. Excel will now treat the cell as pure text and change nothing. * Why does my function display #NAME? The function may point to an add-in function that is not avaliable to this Excel. Most frequently it's an Analysis Toolpack function; go menu Tools Add-Ins and check that there are checks against Analysis Toolpak. Unlike Excel's built-in functions, Add-in functions do not translate themselves to regional language, so american add-in functions are by default unavaliable on a Norwegian computer and vice versa. * Is there way of returning the name of a sheet in a cell without using code? =CELL("Filename",A1) returns the complete file path and sheet name =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) will stip away everything but the sheet name. The file must be saved or the formula will not work. * My spreadsheet does not calculate correctly ! I'm right, Excel's wrong ! There are three common causes for messages like this. 1. What is displayed in the cells is not what's really there. A cell can show 1 (no decimals format) but it may well contain real or calculated values like 0.6 or 1.4. Add or multiply a bunch of those and you're surprised; Excel will calculate with real cell contents, NOT displayed contents. You may choose "precision as displayed" in the tools options menu for a workaround, but make sure you know what you do. 2. A computer use binary numbers, and this has its limitations. It can not represent numbers like 1/10 exact. Numbers like that are rounded to nearest 15 significant decimal digits, and Excel will be "wrong" around 15th-16th digit. Some operations suffer from this, and some boolean tests (tests that may appear as 0.1=0.1) can return False because of this. "Normal work" like sensible-number budgetting and day-to-day math is usually not affected, but this may not be the tool for advanced science. 3. You are using Excel's statistical functions. Some of those are not good enough. LINEST() (also SLOPE(), INTERCEPT(), VAR(), STDEV(), LOGEST(), TREND(), FORECAST(), etc.) uses a numerically unstable algorithm. You're right, Excel's wrong. Links to backgrounders and tools at /Excel/Statistics/ * My spreadsheet does not calculate at all ! Calculation is set to Manual, alter this in Tools Options menu. International issues: These functions are in english, and you can not enter them i a Swedish Excel as is. Run this macro: Sub EnterEnglishFunction() ActiveCell.Formula = InputBox("English function:") End Sub paste the function in and OK, and in most cases it translates. **** Dates and times **** Very very many Excel questions are about dates and times. Chip Pearson's webpage /excel/datetime.htm will give you understanding of how this works in Excel , and it has lots of useful samples. Here are a very few common questions: * How do I add times together ? Just add together just like any number (=A1+A2+A3). Use custom number format [h]:mm in the result cell to prevent rollover at 24 hours * How do I subtract time? Just subtract, like =B1-A1. Use Calendar format 1904 if you need to display negative results; Tools/Options/Calculation and check the 1904 Date System checkbox. Note that 1904 calendar will offset all dates by 4 years 1 day, so be careful. * I need to calculate a column with hh:mm (formatted for TIME) against a hour rate. So 0:45 minutes needs to be calculated against 120 per hour - with an answer of 90. Now it says 3.75 ??? 1 is a day; 24 hours. So one hour is 1/24. =A1*B1*24 will bring the desired result. Format result cell as currency or number (it tends to pick the date format by default). * When I copy-paste dates, they end up one day wrong. * When I copy-paste dates, they end up four years wrong. One workbook is using 1904 calendar, the other one 1900 (in the first example, years are not displayed, but definitely present). Go menu Tools/Options/Calculation and make them equal, preferrably also correct if you know what the dates was supposed to be. * When I try to sum the time data in the format: 5:20, 12:02, 20:12 etc. I get the value that is the real sum minus N*24, eg. 2:07 instead of 50:07. Use custom number format [h]:mm to prevent rollover at 24 hours * I'm adding up a large number of cells with seconds in them, i.e... 25, 50 47, etc... the result I would like is 1:10, 1:50: 2:03 Since XL stores times as fractions of days, in order to convert integers into times you need to divide the sum by (24*60*60) or 86,400 * How do I add 3 months to a date ? =DATE(YEAR(A1),MONTH(A1)+3,DAY(A1)) * Could someone give me the series of worksheet functions that would emulate EOMONTH; last day of month? The last day of the month equals the zero'th of next month for some strange reason: =DATE(YEAR(A1),MONTH(A1)+1,0) * Excel thinks 1900 is a leap year. It's not. Yes it does and it's not. **** Macros, VBA functions **** * I have a user defined function that doesn't recalculate. Include all the cells that your UDF depends on in the argument list. Or enter this as the first statment in your Function: Application.Volatile This will cause the function to be executed whenever a calculation occurs in the workbook. * All of a sudden, when I open the file, it asks if I want to "Enable or Disable a Macro". There are no macros in this workbook. A macro has been added and then removed, leaving an empty module. Open the file, right click on a sheet tab and choose View Code. Look for modules and delete them. Empty modules trigger the macro query, as does an actual macro. While there, make sure all other object's modules are completely empty. *When a button is drawn onto a sheet the assign macro is not displayed. When right-clicking on the button the "assign macro" context menu item is no present. There are buttons and there are buttons, from the Forms toolbar or from the Control Toolbox. If "assign macro" is no option then it's the second kind. Choose "View code" and call your macro from it like this: Private Sub CommandButton1_Click() Call Macro1 End Sub * Some of the functions that we want our macros to accomplish are not possible without first unprotecting the worksheet/workbook with the password. Worksheets("MySheet").Unprotect password:="drowssap" 'your code here Worksheets("MySheet").Protect password:="drowssap" Be sure to protect your macro code to hide the sheet password. * I want Excel to run this macro automatically every time the Excel file is opened. Place the code in (or call it from) the Workbook_open event of the ThisWorkbook module in the VB editor. Or simply name your macro Auto_Open. If you choose to use both then Workbook__open will run before Auto_open. Auto_open will not run if the workbook is opened by another macro, you must use the RunAutoMacros method. Contrary; Workbook_open will run if the workbook is opened by a macro, you must use Application.EnableEvents = False to prevent it. * I want to add a toolbar to my spreadsheet that when clicked, brings up Userform1. Assing the toolbar button to this macro, which should be in a standard VBA module: Sub ShowForm () Userform1.Show End Sub * I want to show a userform each time my file is opened. Combine the two solutions above: Private Sub Workbook_Open() UserForm1.Show End Sub or Sub Auto_open() UserForm1.Show End Sub See Chip Pearson's /excel/events.htm for detail and many more useful events. * Can I ask my user for confirmiation before executing the macro ? Sub AskAndDo() If MsgBox("Are you sure ?", vbYesNo + vbQuestion) = _ vbNo Then Exit Sub 'Code goes here instead of MsgBox "Actions here" End Sub * Can I have my Macro make Excel NOT to ask "the file already exists, do you want to overwrite" type of questions ? Application.DisplayAlerts = False 'code to save, overwrite, delete, whatever goes here Application.DisplayAlerts = True * Is it possible to call a macro from the condition true or false side of a worksheet formula? ie. if(a2="ok",Run macro1,run macro2) Basically, the answer is No. You can write functions in VBA that you can call from worksheet cells, but these functions can only return a value. They can't modify other cells or alter any part of the Excel environment. (See next Q for a macro solution to the problem) * How do I run a macro everytime a certain cell changes it's value? There is an event called Worksheet_Change which is triggered when a value is entered (it will not fire when a formula result changes). One of the arguments to this event is 'Target' which is a reference to what changed. Since this event will occur whenever a value changes - you can use the target to see if it is the cell you are interested in: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("C5")) Is Nothing Then Exit Sub Else 'The cell you are monitoring has changed! 'Do whatever you need to do... End If End Sub * How do I find the first empty cell in A column ? If ActiveSheet.UsedRange.Count < 2 Then MsgBox 1 Else MsgBox Columns("A:A").Find(What:="", LookAt:=xlWhole).Row End If * How do I find the cell below the last entry in A column ? MsgBox Cells(65000, 1).End(xlUp).Row + 1 (This will return 2 on an empty A column) * How do I find the last row in my spreadsheet ? MsgBox ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row * I want to loop through all selected cells and perform an operation on each of them. Sub DoOnSelection() Dim oCell As Range For Each oCell In Selection oCell.Font.Bold = True Next End Sub * I want to loop through all worksheets and perform an operation on each of them (unprotecting or whatever). Sub AllSheets() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets MsgBox ws.Name Next End Sub * I want to loop through all workbooks in a folder and perform an operation on each of them. Sub AllFolderFiles() Dim wb As Workbook Dim TheFile As String Dim MyPath As String MyPath = "C:\Temp" ChDir MyPath TheFile = Dir("*.xls") Do While TheFile < "" Set wb = Workbooks.Open(MyPath & "\" & TheFile) MsgBox wb.FullName wb.Close TheFile = Dir Loop End Sub * How can I tell if a file exists in a spesific folder ? Function bFileExists(rsFullPath As String) As Boolean bFileExists = Len(Dir$(rsFullPath)) End Function * How can I tell if a spesific workbook is open ? Function bWorkbookIsOpen(rsWbkName As String) As Boolean On Error Resume Next bWorkbookIsOpen = Len(Workbooks(rsWbkName).Name) End Function * I want to let the user select a file within my macro. Sub SelectWebPageToOpen() Dim ThePage As Variant ThePage = _ Application.GetOpenFilename("Webpage (*.htm*), *.htm*", _ , "Pick one:") If ThePage = False Then MsgBox "You cancelled" Else MsgBox "Do something with file " & CStr(ThePage) End If End Sub * I want to let the user enter a "Save As" location in my macro. Sub SelectSaveFileName() Dim TheFile As Variant TheFile = Application.GetSaveAsFilename("C:\Temp\File.xls", _ "Workbook (*.xls), *.xls", , "Your choice:") If TheFile = False Then MsgBox "You cancelled" Else MsgBox "Do something with file " & CStr(TheFile) End If End Sub * Is there a way to hide the process of executing macro? Application.ScreenUpdating = False 'code here Application.ScreenUpdating = True *Is there a way to protect the macros I create so people can't see or alter them? Go to Tools VBAProject properties, lock the project for viewing, and enter a password. * How can I unprotect a VBA project using code ? You can not. A workaround is to simulate keystrokes with the SendKeys method. * How do I close a file/close Excel with a macro ? ActiveWorkbook.Close savechanges:=False 'true ??? will close the active workbook Workbooks("mywkbk.xls").Close savechanges:=False 'true ??? will close mywkbk.xls ThisWorkbook.Close savechanges:=False 'true ??? will close the workbook that holds the code that's running. Application.Quit will close all of Excel. Be careful with this one. **** I didn't find my answer here, now what ? **** First, do a search on /advanced_group_search to see wether a similar question has been answered before. Use *excel* in the newsgroup field. See 's /dmcritchie/excel/xlnews.htm for more on archive search. If no luck, post a question. Please follow these guidelines for a best possible result: * Tell us what versions of Excel and Windows you're using * Use an illustrative subject line, not "Excel problem" or "Help" * Don't post to more than one group. People don't like wasting time helping you if your problem is already solved elsewhere. * If you've already tried using some formulas or VBA, include what you've already tried. You may be very close. * Please don't ask us to email and don't follow up answers by direct email unless you are invited to. * Finally; do not attatch files. Attachments are frowned upon for a variety of reasons: - newsgroup Bloat. - download time. Many (most?) people pay their phone company a per-minute rate for connections. - virus concerns. Many won't or aren't allowed to open such files. - they takes up space on individual hard drives - they are not stored on newsgroup archives. - Take the time to explain your problem. Who knows, by writing out the question, you may even figure it out yourself. This list is condensed from Chip Pearson's webpage /excel/newposte.htm. **** Good resources on the web **** There is a very good Excel functions workbook by Peter Noneley at /noneley/ . Recommended. Comp.Apps.Sprreadsheets FAQ is located at /faqs/spreadsheets/faq/ .That one's stuffed with good links, some may be too old though. There are many good Excel webpages, and MVPs' / Frequent posters' signature addresses are all worth a visit. Instead of creating yet another links collection, let's just say Start Here: -walk.com/ss/excel/links/index.htm Finally: * What is an MVP and which exams do I take to become one ? MVP is an award that Microsoft give those who help people with using MS products and do it well. So stay here and provide lots of brilliant answers, then see what happens. There are no other exams than "practice, practice, practice". The MVP program is presented at / |
| 4163 | If you're using those sort icons on the toolbar, stop it. If you select your complete range (columns and rows), then do Data|sort, you'll be all set. The icons on the toolbar sort what's selected (or the complete range) (if I recall correctly). But I could never guess what xl would guess at. But in xl2002, if you have a single column selected and click one of those icons, xl will stop and yell at you. It'll ask if you want to continue as-is or expand the range. -------- FWIW, I never let xl guess at my sorts, subtotals, or pivottable ranges. I've always found it safer (and gives me a little piece of mind that if anything goes wrong, it's my fault.) Chocolate Sauce wrote: HI. I've just started using Excel 2000 to create a music database. The worksheet is 6 columns across with headings such as artist, cd title, year. I'm trying to keep it as user-friendly as possible, so I'm using comments fields to hold info that doesn't need to be immediately visible & the Find option is picking that info up OK. The problem I'm having is that if for instance I decide to sort the worksheet by year, only that particular column gets sorted, which results in any other info on the related rows (eg the name of the cd produced in that year) getting separated. Once that happens, the worksheet doesn't make any sense. Is there any way that I can keep all the contents of the rows together regardless of which column I sort by? I'm probably missing something very simple (like a brain) but it's a long time since I used Excel so I'd really appreciate some help. If Excel isn't the right tool for the job, I'd welcome suggestions of something I can use instead. TIA. -- Dave Peterson ec35720@msn.com |
| 4474 | In Excel 97 I'm sure I could select all the subtotals in a pivot table field - to format them all the same. In Excel 2000 I don't seem tro be able to do this manually - I have to use a preselected presentation that does not do what I want. Is there any way around this? Hopefully, Gef |
| 4594 | Hi Folks. I'm doing a favour for a friend who manages a group of units. I'm trying to set up a worksheet so that when she enters two dates the form calculates the total amount of days then using a pre-determined figure (rent p/week), calculates the total dollar figure. The figure will appear in a cell where it will then be subtotalled (I've figured this part out - see bottom). eg: if rent = $180 p/week inputting two dates... 06/08/02 to 19/08/02 ...would give a value of $360 (2 weeks), or 06/08/02 to 09/08/02 would equal $102.85 (4 days). Any help would be greatly appreciated as I'm not looking to learn the program but to simply help a friend. p.s I have it where adding the rent figure automatically deducts 7.5% for the management fee + 10% GST and any services within the month, giving the correct figure for the owner. I'm an artist so this was no mean feat =) TIA, Malcom. ================================================================ DESCRIPTION DEBIT CREDIT Unit x/xx xxxxxxxxx xxxxxx, xxxxxxx xxxxx Rent $180 per week Tenant & Tenant xxxxxx 06/08/02 to 19/08/02 $360.00 Tenant & Tenant xxxxxx 20/08/02 to 26/08/02 $360.00 $- $- $- $- SUBTOTAL: $720.00 MANAGEMENT FEE $54.00 GST $5.40 MISC $- MISC $- MISC $- TOTAL INC. GST $59.40 PAYMENT TO OWNER: $660.60 CHEQUE SENT WITH STATEMENT $720.00 $720.00 ================================================================ |
| 4595 | Malcom, = (B2-A2+1) / 7 * $G$1 The Start Date is in A2, the End Date in B2, and the weekly rate in G1. It's best not to bury numbers like rental rates directly in formulas. Hence the G1 instead of 180. It's better practice to put it into a cell, where it can be labeled in an adjacent cell, for clarity. The absolute reference ($) in the G1 cell reference are needed only if you'll be copying this formula to other rows. Regards from Virginia Beach, EarlK ------------------------------------------------------------- "Malcom" <spacejazz@primus.com.au wrote in message news:e4643ee8.0208111545.50b754fa@posting.google.com... Hi Folks. I'm doing a favour for a friend who manages a group of units. I'm trying to set up a worksheet so that when she enters two dates the form calculates the total amount of days then using a pre-determined figure (rent p/week), calculates the total dollar figure. The figure will appear in a cell where it will then be subtotalled (I've figured this part out - see bottom). eg: if rent = $180 p/week inputting two dates... 06/08/02 to 19/08/02 ...would give a value of $360 (2 weeks), or 06/08/02 to 09/08/02 would equal $102.85 (4 days). Any help would be greatly appreciated as I'm not looking to learn the program but to simply help a friend. p.s I have it where adding the rent figure automatically deducts 7.5% for the management fee + 10% GST and any services within the month, giving the correct figure for the owner. I'm an artist so this was no mean feat =) TIA, Malcom. ================================================================ DESCRIPTION DEBIT CREDIT Unit x/xx xxxxxxxxx xxxxxx, xxxxxxx xxxxx Rent $180 per week Tenant & Tenant xxxxxx 06/08/02 to 19/08/02 $360.00 Tenant & Tenant xxxxxx 20/08/02 to 26/08/02 $360.00 $- $- $- $- SUBTOTAL: $720.00 MANAGEMENT FEE $54.00 GST $5.40 MISC $- MISC $- MISC $- TOTAL INC. GST $59.40 PAYMENT TO OWNER: $660.60 CHEQUE SENT WITH STATEMENT $720.00 $720.00 ================================================================ |
| 4605 | Hi Folks. I'm doing a favour for a friend who manages a group of units. I'm trying to set up a worksheet so that when she enters two dates the form calculates the total amount of days then using a pre-determined figure (rent p/week), calculates the total dollar figure. The figure will appear in a cell (Credit column) where it will then be subtotalled (I've figured this part out - see bottom). eg: if rent = $180 p/week inputting two dates... 06/08/02 to 19/08/02 ...would give a value of $360 (2 weeks), or 06/08/02 to 09/08/02 would equal $102.85 (4 days). As it stands I have it where you enter the dates then enter the figure (credit) which is then calculated accordingly. I'm wanting to be able to just enter the dates and have the figure automagically appear. TIA, Malcom. Sorry about the formatting below. ================================================================ DESCRIPTION DEBIT CREDIT Unit x/xx xxxxxxxxx xxxxxx, xxxxxxx xxxxx Rent $180 per week Tenant names [receipt #] 06/08/02 to 19/08/02 $360.00 Tenant names [receipt #] 20/08/02 to 26/08/02 $360.00 $- $- $- $- SUBTOTAL: $720.00 MANAGEMENT FEE $54.00 GST $5.40 MISC $- MISC $- MISC $- TOTAL INC. GST $59.40 PAYMENT TO OWNER: $660.60 $720.00 $720.00 ================================================================ |
| 4612 | Hi Malcolm, In XL dates are represented as the number of days since 31 December 1899. Thus to find the number of days between any two dates, just subtract one from the other. Unfortunately XL conceals this useful feature by automatically formatting the result as a date (which is not particularly helpful). If however you use the Format / Cells / Number menu and turn it back into a number it should be clear what is happening. Regards, Peter "Malcom" <spacejazz@primus.com.au wrote in message news:e4643ee8.0208111711.24af6b28@posting.google.com... Hi Folks. I'm doing a favour for a friend who manages a group of units. I'm trying to set up a worksheet so that when she enters two dates the form calculates the total amount of days then using a pre-determined figure (rent p/week), calculates the total dollar figure. The figure will appear in a cell (Credit column) where it will then be subtotalled (I've figured this part out - see bottom). eg: if rent = $180 p/week inputting two dates... 06/08/02 to 19/08/02 ...would give a value of $360 (2 weeks), or 06/08/02 to 09/08/02 would equal $102.85 (4 days). As it stands I have it where you enter the dates then enter the figure (credit) which is then calculated accordingly. I'm wanting to be able to just enter the dates and have the figure automagically appear. TIA, Malcom. Sorry about the formatting below. ================================================================ DESCRIPTION DEBIT CREDIT Unit x/xx xxxxxxxxx xxxxxx, xxxxxxx xxxxx Rent $180 per week Tenant names [receipt #] 06/08/02 to 19/08/02 $360.00 Tenant names [receipt #] 20/08/02 to 26/08/02 $360.00 $- $- $- $- SUBTOTAL: $720.00 MANAGEMENT FEE $54.00 GST $5.40 MISC $- MISC $- MISC $- TOTAL INC. GST $59.40 PAYMENT TO OWNER: $660.60 $720.00 $720.00 ================================================================ |
| 4615 | Malcolm You should put your weekly rent figure in a cell so you can change it. Say A1 = weekly rent $180, B1=start date 06/08/02, C1=end date 19/08/02. The formula in D1 is:- (C1-B1)*($A$1/7). This of course has to be formatted as currency with 2 dec places. Cheers, Andrew. You can now drag down the formula. Any change in A1 will automatically reflect in D1. "Malcom" <spacejazz@primus.com.au wrote in message news:e4643ee8.0208111711.24af6b28@posting.google.com... Hi Folks. I'm doing a favour for a friend who manages a group of units. I'm trying to set up a worksheet so that when she enters two dates the form calculates the total amount of days then using a pre-determined figure (rent p/week), calculates the total dollar figure. The figure will appear in a cell (Credit column) where it will then be subtotalled (I've figured this part out - see bottom). eg: if rent = $180 p/week inputting two dates... 06/08/02 to 19/08/02 ...would give a value of $360 (2 weeks), or 06/08/02 to 09/08/02 would equal $102.85 (4 days). As it stands I have it where you enter the dates then enter the figure (credit) which is then calculated accordingly. I'm wanting to be able to just enter the dates and have the figure automagically appear. TIA, Malcom. Sorry about the formatting below. ================================================================ DESCRIPTION DEBIT CREDIT Unit x/xx xxxxxxxxx xxxxxx, xxxxxxx xxxxx Rent $180 per week Tenant names [receipt #] 06/08/02 to 19/08/02 $360.00 Tenant names [receipt #] 20/08/02 to 26/08/02 $360.00 $- $- $- $- SUBTOTAL: $720.00 MANAGEMENT FEE $54.00 GST $5.40 MISC $- MISC $- MISC $- TOTAL INC. GST $59.40 PAYMENT TO OWNER: $660.60 $720.00 $720.00 ================================================================ |
| 4616 | I've just realised you posted the same question under a different heading earlier, for some reason. Anyway, EarlK's answer is essentially the same as mine. "Andrew E Laycock" <andrew@aelaycock.fsnet.co.uk wrote in message news:#X6p1zbQCHA.2128@tkmsftngp08... Malcolm You should put your weekly rent figure in a cell so you can change it. Say A1 = weekly rent $180, B1=start date 06/08/02, C1=end date 19/08/02. The formula in D1 is:- (C1-B1)*($A$1/7). This of course has to be formatted as currency with 2 dec places. Cheers, Andrew. You can now drag down the formula. Any change in A1 will automatically reflect in D1. "Malcom" <spacejazz@primus.com.au wrote in message news:e4643ee8.0208111711.24af6b28@posting.google.com... Hi Folks. I'm doing a favour for a friend who manages a group of units. I'm trying to set up a worksheet so that when she enters two dates the form calculates the total amount of days then using a pre-determined figure (rent p/week), calculates the total dollar figure. The figure will appear in a cell (Credit column) where it will then be subtotalled (I've figured this part out - see bottom). eg: if rent = $180 p/week inputting two dates... 06/08/02 to 19/08/02 ...would give a value of $360 (2 weeks), or 06/08/02 to 09/08/02 would equal $102.85 (4 days). As it stands I have it where you enter the dates then enter the figure (credit) which is then calculated accordingly. I'm wanting to be able to just enter the dates and have the figure automagically appear. TIA, Malcom. Sorry about the formatting below. ================================================================ DESCRIPTION DEBIT CREDIT Unit x/xx xxxxxxxxx xxxxxx, xxxxxxx xxxxx Rent $180 per week Tenant names [receipt #] 06/08/02 to 19/08/02 $360.00 Tenant names [receipt #] 20/08/02 to 26/08/02 $360.00 $- $- $- $- SUBTOTAL: $720.00 MANAGEMENT FEE $54.00 GST $5.40 MISC $- MISC $- MISC $- TOTAL INC. GST $59.40 PAYMENT TO OWNER: $660.60 $720.00 $720.00 ================================================================ |
| 4767 | I have imported golf data from three separate days into Excel 2000. I then use DATA, SUBTOTALS to insert the correct formula to get team totals. However, the formula returns a value of "0". I have to reenter the cells above the formula to get the correct result. Any help??? thanks, tpp |
| 4771 | Excel is not seeing these numbers as true numbers. In a blank cell enter the number 1 Copy that cell. Highlight the cells of golf data Go to Edit Paste Special Under Operation, choose "Multiply" Click OK -- HTH, Laura (at home) "Thomas P. Peacock" <peacockz@pacbell.net wrote in message news:JCh69.4774$zl.213469470@newssvr13.news.prodigy.com... I have imported golf data from three separate days into Excel 2000. I then use DATA, SUBTOTALS to insert the correct formula to get team totals. However, the formula returns a value of "0". I have to reenter the cells above the formula to get the correct result. Any help??? thanks, tpp |
| 4777 | Laura, thanks! That seem to do the trick; now I have somewhere to go to find out why this is happening. tpp "Laura Wilde" <lwilde@new.rr.com wrote in message news:#xTMcizQCHA.2404@tkmsftngp13... Excel is not seeing these numbers as true numbers. In a blank cell enter the number 1 Copy that cell. Highlight the cells of golf data Go to Edit Paste Special Under Operation, choose "Multiply" Click OK -- HTH, Laura (at home) "Thomas P. Peacock" <peacockz@pacbell.net wrote in message news:JCh69.4774$zl.213469470@newssvr13.news.prodigy.com... I have imported golf data from three separate days into Excel 2000. I then use DATA, SUBTOTALS to insert the correct formula to get team totals. However, the formula returns a value of "0". I have to reenter the cells above the formula to get the correct result. Any help??? thanks, tpp |
| 4929 | I use this User Defined Function, which was posted by Tom Ogilvy. To make it respond to a filter change, tie it to the subtotal command. =showfilter(B2)&CHAR(SUBTOTAL(9,B3)*0+32) The above would show the criteria for column B Public Function ShowFilter(rng As Range) 'UDF that displays the filter criteria. 'posted by Tom Ogilvy 1/17/02 Dim filt As Filter Dim sCrit1 As String Dim sCrit2 As String Dim sop As String Dim lngOp As Long Dim lngOff As Long Dim frng As Range Dim sh As Worksheet Set sh = rng.Parent If sh.FilterMode = False Then ShowFilter = "No Active Filter" Exit Function End If Set frng = sh.AutoFilter.Range If Intersect(rng.EntireColumn, frng) Is Nothing Then ShowFilter = CVErr(xlErrRef) Else lngOff = rng.Column - frng.Columns(1).Column + 1 If Not sh.AutoFilter.Filters(lngOff).On Then ShowFilter = "No Conditions" Else Set filt = sh.AutoFilter.Filters(lngOff) On Error Resume Next sCrit1 = filt.Criteria1 sCrit2 = filt.Criteria2 lngOp = filt.Operator If lngOp = xlAnd Then sop = " And " ElseIf lngOp = xlOr Then sop = " or " Else sop = "" End If ShowFilter = sCrit1 & sop & sCrit2 End If End If End Function Roberto Corrêa wrote: Hi All. I´m looking for a way to set the cell "A1" value as the same criteria choosen by the user, in an AutoFiltered table. I´ve tried to use the function "=DESLOC()" in Portuguese (something like ´=OffSet()´ in English) as cell A1 formula. But this formula doesn´t work. It always retrieves the first line cell value, even though the AutoFilter is activated and the first line is hidden. Thanks for any help. Roberto Corrêa -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 5026 | David, Your layout will preclude the use of many Excel commands, like Subtotals. You might want to get it into a standard database-style layout. Product ID Forecast Cost Margin Month 001 900 100 20 Jan 001 800 100 20 Feb . . . 001 1200 100 20 Dec 002 1300 150 27 Jan etc. You'll have more rows, but less columns. You can now sort this in several ways, and use Subtotal for group totals (by product, or by month, etc). Or AutoFilter, in conjunction with =SUBTOTAL( ) in a cell for ad hoc totals. With this layout, you can have a great time with Excel's database capabilities (Sort, Autofilter, Advanced filter, Database functions, Group & Outline). Regards from Virginia Beach, EarlK ------------------------------------------------------------- "David Pedley" <david@pedley.ws wrote in message news:102c01c24536$7ce4b8b0$9be62ecf@tkmsftngxa03... For accounting spreadsheets, I frequently need to repeat a sequence of rows several times and then produce a total of each of the rows in the sequence. eg [much simplified]: Months in columns; First Seq - Product 1 - First Row 'Sales Forecast', Second Row 'Cost' (maybe calculated from individual product percentages), Third Row 'Margin' Second Sequence - same for product 2 ... nth Sequence - Product n Then n+1th sequence - Sum of all product sales, sum of all costs, sum of all margins Problem - how to produce the final totals sequence without explicitly summing each row. There must be a way using Grouping or Outlines but I can't find it. Using Excel 2000 under Win98. Will appreciate any ideas. David Pedley |
| 5104 | Hi Fran I wondered why you were keeping your data on bi-monthly sheets? UK VAT is normally returned either Monthly or Quarterly, hence I cannot immediately see the advantage of bi-monthly recording, but perhaps you are not in the UK or are subject to some special scheme of which I am unaware. If you kept all of your data on a single Sheet, you would have far more opportunity to carry out many types of analysis. Applying a simple Data=Autofilter would give drop downs on each column heading, and you could select any Trader Name or choose the Top 10 option. As Brian said, a Pivot Table of the data would not only summarise your data by all traders, it would also permit you to select the Top 10. If you just copied and pasted all of your data to a single sheet, then I would add an additional column (G) titled Month. In cell G2 enter =IF(A2="","",MONTH(A2)) and copy down the column to cover the range of your data including entries for the remainder of the year. This assumes that the entries in column A are true date entries, not just text entries. This would make it easy for you to pull up the VAT transactions for any single Month very easily, or any 2 or three month periods. If you inserted a row above your headings, you could enter into cell E1 =SUBTOTAL(9,E3:E5000) Copy this across through cells F1 and G1 This will show you the totals for any selection you make, either by VAT month, or Trader. -- Regards, Roger Govier Technology 4 U "Fran" <hollyind@yahoo.co.uk wrote in message news:Abc79.9357$zX3.13871@news.indigo.ie... Thanks for the reply Brian. I basically have a purchases ledger in excel 97 that l use for calculating a vat return. I was also hoping that l could use the data to create summary sheets for the most popular companies l deal with. EG Jan Feb Date Name Ref Code Net Vat Gross 12/1 Acme Industries P123 a 100 12.5 112.5 13/1 DAM Inc P124 a 200 25 225 14/1 Ex Ltd P125 a 150 37.5 187.5 16/1 Acme Industries P126 a 500 62.5 562.5 Summary sheet Acme Industies 12/1 P123 a 100 12.5 112.5 16/1 P126 a 500 62.5 562.5 I don't know whether this is any clearer ;-) Cheers Fran "BrianB" <brian.anon@tiscali.co.uk wrote in message news:95c1d597.0208160015.c0f477a@posting.google.com... Not too clear what you are after. Could have a look at the =MAX() worksheet function. Pivot Tables have a "Top 10" selection option. Regards BrianB ===================================== "Fran" <hollyind@yahoo.co.uk wrote in message news:<7kB69.8972$zX3.13295@news.indigo.ie... I have a workbook, with Bi-monthly sheets showing purchases made on the appropriate sheets. The data columns are Date, Name, Ref. No, Tax code, Nett, Vat and Gross. The sheets are identical and are used for Vat (tax) analysis. Is it possible to add separate sheets that would show the data relating the most popular " names" from all the bi-monthly sheets and update them as the data is entered. TIA Fran |
| 5172 | Let's assume your countries are in col. A and the second column you wish to filter from is col. B. 1. Filter for Ireland in the col. A and then by your second criterion in col. B 2. Use this formula: =(SUBTOTAL(3,B:B)-1)/COUNTIF(A:A,"Ireland") The first part counts only visible rows (subtract 1 for the header) and the second part counts all rows equal "Ireland", visible and non-visible. HTH Jason Atlanta, GA -----Original Message----- Hi, Is there a way to use filter result in another formula or perhaps just copy it as data in another cell? I have a list of countries and when I filter them using auto filter (e.g. I get 13 records for Ireland) I would like to use the word Ireland in another formula (countif). I have to count the number of records for each selected country and then use another filter on a neighbouring column. I would then like to calculate percentage using the two filtered results. Example: After first filter I get 13 records for Ireland. After second filter the number goes down to 8. I then simple divide 8 by 13 to get 61.5%. I want it done automatically just by choosing first and second filters. How? Best regards, Primoz . |
| 5176 | Thanks, but I would like to select word "Ireland" (or any other country name) from the auto filter list, not by entering it in the formula. I am sending the spreadsheet to someone else and they have to be able to set the two filters using autofilter and get the proper result. Primoz "Jason Morin" <jason.morin@us.exel.com wrote in message news:425201c24781$72ad52b0$a4e62ecf@tkmsftngxa06... Let's assume your countries are in col. A and the second column you wish to filter from is col. B. 1. Filter for Ireland in the col. A and then by your second criterion in col. B 2. Use this formula: =(SUBTOTAL(3,B:B)-1)/COUNTIF(A:A,"Ireland") The first part counts only visible rows (subtract 1 for the header) and the second part counts all rows equal "Ireland", visible and non-visible. HTH Jason Atlanta, GA -----Original Message----- Hi, Is there a way to use filter result in another formula or perhaps just copy it as data in another cell? I have a list of countries and when I filter them using auto filter (e.g. I get 13 records for Ireland) I would like to use the word Ireland in another formula (countif). I have to count the number of records for each selected country and then use another filter on a neighbouring column. I would then like to calculate percentage using the two filtered results. Example: After first filter I get 13 records for Ireland. After second filter the number goes down to 8. I then simple divide 8 by 13 to get 61.5%. I want it done automatically just by choosing first and second filters. How? Best regards, Primoz . |
| 5183 | You could do this with a User defined Function. Tom Ogilvy wrote the ShowFilter UDF code that is included at the end of this post. I store it in my personal.xls workbook, which I always have open. 1. Add the ShowFilter code to a regular module sheet (e.g. in Personal.xls) 2. On the sheet with the filter, add the following formula to a cell a few columns to the right of the database (cell K1 in this example): =PERSONAL.xls!showfilter(D1)&CHAR(SUBTOTAL(9,D2)*0+32) This will show the criteria used in column D 3. In the cell below, enter the following formula, changing the cell references to match your database, and the cell with the UDF formula: =COUNTIF($D$1:$D$50,MID(K1,2,LEN(K1)-2))/(SUBTOTAL(3,$F$1:$F$50)-1) This works if only one criterion is selected in column D (e.g. Ireland). It counts the visible rows with data in the range F1:F50, and subtracts one for the heading. The number of rows in the table that meet the criterion in cell K1 is divided by this number. Public Function ShowFilter(rng As Range) 'UDF that displays the filter criteria. 'posted by Tom Ogilvy 1/17/02 'To make it respond to a filter change, tie it to the subtotal command. '=showfilter(B2)&CHAR(SUBTOTAL(9,B3)*0+32) 'So the above would show the criteria for column B Dim filt As Filter Dim sCrit1 As String Dim sCrit2 As String Dim sop As String Dim lngOp As Long Dim lngOff As Long Dim frng As Range Dim sh As Worksheet Set sh = rng.Parent If sh.FilterMode = False Then ShowFilter = "No Active Filter" Exit Function End If Set frng = sh.AutoFilter.Range If Intersect(rng.EntireColumn, frng) Is Nothing Then ShowFilter = CVErr(xlErrRef) Else lngOff = rng.Column - frng.Columns(1).Column + 1 If Not sh.AutoFilter.Filters(lngOff).On Then ShowFilter = "No Conditions" Else Set filt = sh.AutoFilter.Filters(lngOff) On Error Resume Next sCrit1 = filt.Criteria1 sCrit2 = filt.Criteria2 lngOp = filt.Operator If lngOp = xlAnd Then sop = " And " ElseIf lngOp = xlOr Then sop = " or " Else sop = "" End If ShowFilter = sCrit1 & sop & sCrit2 End If End If End Function Moses KA wrote: Hi, Is there a way to use filter result in another formula or perhaps just copy it as data in another cell? I have a list of countries and when I filter them using auto filter (e.g. I get 13 records for Ireland) I would like to use the word Ireland in another formula (countif). I have to count the number of records for each selected country and then use another filter on a neighbouring column. I would then like to calculate percentage using the two filtered results. Example: After first filter I get 13 records for Ireland. After second filter the number goes down to 8. I then simple divide 8 by 13 to get 61.5%. I want it done automatically just by choosing first and second filters. How? Best regards, Primoz -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 5187 | Sorry, I just reread your message -- the formula should be: =(SUBTOTAL(3,$F$1:$F$50)-1)/COUNTIF($D$1:$D$50,MID(K1,2,LEN(K1)-2)) Debra Dalgleish wrote: You could do this with a User defined Function. Tom Ogilvy wrote the ShowFilter UDF code that is included at the end of this post. I store it in my personal.xls workbook, which I always have open. 1. Add the ShowFilter code to a regular module sheet (e.g. in Personal.xls) 2. On the sheet with the filter, add the following formula to a cell a few columns to the right of the database (cell K1 in this example): =PERSONAL.xls!showfilter(D1)&CHAR(SUBTOTAL(9,D2)*0+32) This will show the criteria used in column D 3. In the cell below, enter the following formula, changing the cell references to match your database, and the cell with the UDF formula: =COUNTIF($D$1:$D$50,MID(K1,2,LEN(K1)-2))/(SUBTOTAL(3,$F$1:$F$50)-1) This works if only one criterion is selected in column D (e.g. Ireland). It counts the visible rows with data in the range F1:F50, and subtracts one for the heading. The number of rows in the table that meet the criterion in cell K1 is divided by this number. Public Function ShowFilter(rng As Range) 'UDF that displays the filter criteria. 'posted by Tom Ogilvy 1/17/02 'To make it respond to a filter change, tie it to the subtotal command. '=showfilter(B2)&CHAR(SUBTOTAL(9,B3)*0+32) 'So the above would show the criteria for column B Dim filt As Filter Dim sCrit1 As String Dim sCrit2 As String Dim sop As String Dim lngOp As Long Dim lngOff As Long Dim frng As Range Dim sh As Worksheet Set sh = rng.Parent If sh.FilterMode = False Then ShowFilter = "No Active Filter" Exit Function End If Set frng = sh.AutoFilter.Range If Intersect(rng.EntireColumn, frng) Is Nothing Then ShowFilter = CVErr(xlErrRef) Else lngOff = rng.Column - frng.Columns(1).Column + 1 If Not sh.AutoFilter.Filters(lngOff).On Then ShowFilter = "No Conditions" Else Set filt = sh.AutoFilter.Filters(lngOff) On Error Resume Next sCrit1 = filt.Criteria1 sCrit2 = filt.Criteria2 lngOp = filt.Operator If lngOp = xlAnd Then sop = " And " ElseIf lngOp = xlOr Then sop = " or " Else sop = "" End If ShowFilter = sCrit1 & sop & sCrit2 End If End If End Function Moses KA wrote: Hi, Is there a way to use filter result in another formula or perhaps just copy it as data in another cell? I have a list of countries and when I filter them using auto filter (e.g. I get 13 records for Ireland) I would like to use the word Ireland in another formula (countif). I have to count the number of records for each selected country and then use another filter on a neighbouring column. I would then like to calculate percentage using the two filtered results. Example: After first filter I get 13 records for Ireland. After second filter the number goes down to 8. I then simple divide 8 by 13 to get 61.5%. I want it done automatically just by choosing first and second filters. How? Best regards, Primoz -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 5201 | I would like to sort a list in multiple orders and report on it with totals and subtotals. Pivot tables will work if I just want summaries (i.e. no detail records). Data | Subtotals will work with one particular sort order and subtotals. But what if I want - multiple reports with different sort orders on each report, AND - to keep the detail records on each report? A simplified sample might have fields such as: Salesperson Customer Job WinProbability Amount Expected (i.e. WinProbability * Amount) StartDate EndDate Months AmountPerMonth (i.e. Amount/Months) Description NextTask where - one report sorts and subtotals by customer, probability and job and - another report sorts and subtotals by salesperson, probability and job and - another report sorts and subtotals by probability, job. Totals and subtotals would be given for Amount, Expected and Number of Detail Records. |
| 5214 | Pivot Tables do summarise the totals for you, and, you can perform a drill down on any item to see all of the detail making up that figure. Just double click on any cell within your pivot table, and a new sheet will open with each line of data making up that particular value. Delete the sheet when you are finished with it. In order to achieve your different views, create three different Pivot Tables arranged and sorted in the orders you want. When setting up PT's 2 and 3, click the radio button to choose Another Pivot Table as the source of your data. All three reports will then be based upon the one dataset. -- Regards, Roger Govier Technology 4 U "Grod" <ggrothendieck@volcanomail.com wrote in message news:ffd662ea.0208190655.478f2d1@posting.google.com... I would like to sort a list in multiple orders and report on it with totals and subtotals. Pivot tables will work if I just want summaries (i.e. no detail records). Data | Subtotals will work with one particular sort order and subtotals. But what if I want - multiple reports with different sort orders on each report, AND - to keep the detail records on each report? A simplified sample might have fields such as: Salesperson Customer Job WinProbability Amount Expected (i.e. WinProbability * Amount) StartDate EndDate Months AmountPerMonth (i.e. Amount/Months) Description NextTask where - one report sorts and subtotals by customer, probability and job and - another report sorts and subtotals by salesperson, probability and job and - another report sorts and subtotals by probability, job. Totals and subtotals would be given for Amount, Expected and Number of Detail Records. |
| 5245 | The way to do this manually is to add subtotals to any column (Data, Subtotals) then remove the outline, then select all of the column with the subtotal formulas and use Goto Special to select just the formulas, which you can then clear. That should leave just the empty rows you want. -- Jim Rech Excel MVP |
| 5364 | I have a large spreadsheet with numerous rows and columns that I want to summarize in a Pivot Table. There are numbers in four columns (A, B, C and D) that I wish to summarize in the pivot table as follows: Name A B C D Simple example of source data: Name A B C D Smith 10 20 40 50 Smith 20 50 30 60 Jones 20 60 50 90 Jones 20 40 20 40 If I put A, B, C and D in the Data field of the Layout section of the Pivot Table wizard, they end up in row format as follows: Smith A Smith B Smith C Smith D Jones A etc. How do I get them to summarize in column format? I tried to move them into the Column section of the Layout portion of the Pivot Table Wizard; but that did not work. The Pivot Table Wizard would not let me repeat these fields in both the Column and Data sections of the Pivot Table wizard. In addition, it would not allow me to leave the Data section blank. Finally, in my actual spreadsheet, I have several fields in the Row section. The Pivot Table wizard is adding subtotals that I do not want. How do I get rid of these subtotals? Thanks in advance for your help! |
| 5367 | To get the Data fields displayed horizontally, drag the Data button onto the cell that contains the word 'Total' To eliminate the subtotals, double-click on a column field button. For Subtotals, choose None, click OK. SM wrote: I have a large spreadsheet with numerous rows and columns that I want to summarize in a Pivot Table. There are numbers in four columns (A, B, C and D) that I wish to summarize in the pivot table as follows: Name A B C D Simple example of source data: Name A B C D Smith 10 20 40 50 Smith 20 50 30 60 Jones 20 60 50 90 Jones 20 40 20 40 If I put A, B, C and D in the Data field of the Layout section of the Pivot Table wizard, they end up in row format as follows: Smith A Smith B Smith C Smith D Jones A etc. How do I get them to summarize in column format? I tried to move them into the Column section of the Layout portion of the Pivot Table Wizard; but that did not work. The Pivot Table Wizard would not let me repeat these fields in both the Column and Data sections of the Pivot Table wizard. In addition, it would not allow me to leave the Data section blank. Finally, in my actual spreadsheet, I have several fields in the Row section. The Pivot Table wizard is adding subtotals that I do not want. How do I get rid of these subtotals? Thanks in advance for your help! -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 5391 | please help; an Excel file will only sort by row; the first row has column names, the subtotals have been removed; the sort by ascending order has been clicked, only the data below the first title row has been selected for sort; all other Excel files will give the option to sort by column when the sort button is pressed; has anyone any idea why only the row option appears; thanks in advance. |
| 5416 | Hi, I've tried to make this UDF work, but Microsoft Visual Basic reports: Invalid outside procedure. Word Set (from set sh = rng.Parent) is highlighted. I am a beginner when it comes to VBA so any help will be appreciated. Thanks, Primoz "Debra Dalgleish" <dsd@contextures.com wrote in message news:3D60FAE7.1090907@contextures.com... You could do this with a User defined Function. Tom Ogilvy wrote the ShowFilter UDF code that is included at the end of this post. I store it in my personal.xls workbook, which I always have open. 1. Add the ShowFilter code to a regular module sheet (e.g. in Personal.xls) 2. On the sheet with the filter, add the following formula to a cell a few columns to the right of the database (cell K1 in this example): =PERSONAL.xls!showfilter(D1)&CHAR(SUBTOTAL(9,D2)*0+32) This will show the criteria used in column D 3. In the cell below, enter the following formula, changing the cell references to match your database, and the cell with the UDF formula: =COUNTIF($D$1:$D$50,MID(K1,2,LEN(K1)-2))/(SUBTOTAL(3,$F$1:$F$50)-1) This works if only one criterion is selected in column D (e.g. Ireland). It counts the visible rows with data in the range F1:F50, and subtracts one for the heading. The number of rows in the table that meet the criterion in cell K1 is divided by this number. Public Function ShowFilter(rng As Range) 'UDF that displays the filter criteria. 'posted by Tom Ogilvy 1/17/02 'To make it respond to a filter change, tie it to the subtotal command. '=showfilter(B2)&CHAR(SUBTOTAL(9,B3)*0+32) 'So the above would show the criteria for column B Dim filt As Filter Dim sCrit1 As String Dim sCrit2 As String Dim sop As String Dim lngOp As Long Dim lngOff As Long Dim frng As Range Dim sh As Worksheet Set sh = rng.Parent If sh.FilterMode = False Then ShowFilter = "No Active Filter" Exit Function End If Set frng = sh.AutoFilter.Range If Intersect(rng.EntireColumn, frng) Is Nothing Then ShowFilter = CVErr(xlErrRef) Else lngOff = rng.Column - frng.Columns(1).Column + 1 If Not sh.AutoFilter.Filters(lngOff).On Then ShowFilter = "No Conditions" Else Set filt = sh.AutoFilter.Filters(lngOff) On Error Resume Next sCrit1 = filt.Criteria1 sCrit2 = filt.Criteria2 lngOp = filt.Operator If lngOp = xlAnd Then sop = " And " ElseIf lngOp = xlOr Then sop = " or " Else sop = "" End If ShowFilter = sCrit1 & sop & sCrit2 End If End If End Function |
| 5424 | may2, select your data, do datasort and click the options button, select sort top to bottom, click OK.. If somebody sorted that file using sort left to right and then saved that file, then that sort will be default for that particular file. So after you have changed the sort orientation, save the file to get column sorting for this file.. -- Regards, Peo Sjoblom may2 wrote: please help; an Excel file will only sort by row; the first row has column names, the subtotals have been removed; the sort by ascending order has been clicked, only the data below the first title row has been selected for sort; all other Excel files will give the option to sort by column when the sort button is pressed; has anyone any idea why only the row option appears; thanks in advance. |
| 5477 | Are you trying to get a complete list of every combination of Dept and Job? If yes, then I'd use Data|Pivottable. But you have to prepare the data a little by filling in those blank cells in column A: Select Column A Hit F5 (or Edit|goto), then click special, then check Blanks. type = hit the uparrow hit ctrl-Enter This filled each empty cell with a formula that says to use the value right above it. Now you have the Dept#, job # on every row that has a # of Days entered. Now Select that Range, Copy and paste|Special Values right on top of it. We don't want deleting any rows to affect our formulas--so we'll get rid of the formulas. ====== You have a couple of options to get rid of the bad data. You can ignore it in the pivot table or eliminate it now. I like to clean up the data first. Select columns A:C and do Data|Filter|autofilter Filter on column C. Select Custom, blanks. Delete those rows by selecting them and rightclick|delete. (xl97 and above will delete the visible rows only. xl95 <version 7, as I sometimes don't recall <vbg will delete all the rows you have selected. You have to select the range, F5|Special|visible cells only to get that version to work the way you want.) If Crystal put other headers (page headers???), now's a nice time to clean them up using the same technique. After you've got the data scrubbed, you can select the bottom right corner to the top right corner and do Data|Pivottable (name changes with versions of excel, but it's got Pivot in it!) Follow the wizard. But since you selected the range first, you can just Ok past the first couple of dialogs. (I use xl2002, so the dialogs might not match up, but they'll be close). You're going to see an button (somewhere) for Layout. Click on this. Drag the Dept to the Row field. Drag the Days to the Data field. Choice time! Drag the Job to the row field if you want it to look like this: Sum of Days Dept. Job Total 1100 2000001 30 2000002 40 2000003 60 2000004 120 2000005 200 1100 Total 450 1101 2000001 10 2000002 40 2000003 60 2000004 80 2000005 50 1101 Total 240 1103 2000001 20 2000002 40 2000003 60 2000004 40 2000005 50 1103 Total 210 Grand Total 900 Drag it to the Column Field if you something like: Sum of Days Job Dept. 2000001 2000002 2000003 2000004 2000005 Grand Total 1100 30 40 60 120 200 450 1101 10 40 60 80 50 240 1103 20 40 60 40 50 210 Grand Total 60 120 180 240 300 900 The real neat thing about pivottables is those grey looking buttons on the worksheet are hot. You can drag them a lot of different places and show different summaries. Right click on a few things and you can hid subtotals. If you dragged a field into the data box, you'll notice that it either said sum (if all numeric) or Count (if at least one cell wasn't a number). You can double click on that and change it to a few different options. ========= Could this be done by a formula? (Well, not by me!) If you do this a lot, you can record a macro when you do it once. About the only thing you'll have to change is the range that the pivottable is based. (If you decide to do this, post back if you need help.) ======== Here are some nice references: John Peltier's pictures at: /jonpeltier/Excel/Pivots/pivottables.htm John Walkenbach also has some at: -walk.com/ss/excel/files/general.htm (look for Tony Gwynn's Hit Database) Chip Pearson keeps Harald Staff's notes at: /excel/pivots.htm MS has some at (xl2000 and xl2002): /downloads/2000/XCrtPiv.aspx /assistance/2002/articles/xlconPT101.aspx Joan M wrote: I have output from cyrstal reports which I am exporting to an excel spreadsheet. The information is sorted in 3 columns. The first column contains the department - first condition to find. The second column contains the job - next condtion to find. The third column is the answer - total days. I need a formula for a 2nd spreadsheet that, first finds the department, then the job, then the days that match the job to the department. Each department can be working on the same jobs, so that the jobs are repeated throughout the spreadsheet. The problem is to first match the department, then the job, then the hours related to that job for that department. spreadsheet 1 (download of crystal report)(information exports in this manner) Dept. Job Days 1100 2000001 10 2000002 20 2000003 30 2000004 40 2000005 50 1200 2000001 60 2000002 70 2000003 80 2000004 90 2000005 100 Spreadsheet 2 1100 1200 2000001 10 60 need formula for 2nd and 3rd column here (10, 60) 2000002 20 70 2000003 30 80 2000004 40 90 2000005 50 100 Help please! Joan Moriyama -- Dave Peterson ec35720@msn.com |
| 5520 | Thanks, it works. Primoz "Dave Peterson" <ec35720@msn.com wrote in message news:3D6407DD.B78A0304@msn.com... I think you missed a couple of lines when you copied from the newsgroup post. You want everything from: Public Function ShowFilter(rng As Range) to End Function (including both of those lines.) Moses KA wrote: Hi, I've tried to make this UDF work, but Microsoft Visual Basic reports: Invalid outside procedure. Word Set (from set sh = rng.Parent) is highlighted. I am a beginner when it comes to VBA so any help will be appreciated. Thanks, Primoz "Debra Dalgleish" <dsd@contextures.com wrote in message news:3D60FAE7.1090907@contextures.com... You could do this with a User defined Function. Tom Ogilvy wrote the ShowFilter UDF code that is included at the end of this post. I store it in my personal.xls workbook, which I always have open. 1. Add the ShowFilter code to a regular module sheet (e.g. in Personal.xls) 2. On the sheet with the filter, add the following formula to a cell a few columns to the right of the database (cell K1 in this example): =PERSONAL.xls!showfilter(D1)&CHAR(SUBTOTAL(9,D2)*0+32) This will show the criteria used in column D 3. In the cell below, enter the following formula, changing the cell references to match your database, and the cell with the UDF formula: =COUNTIF($D$1:$D$50,MID(K1,2,LEN(K1)-2))/(SUBTOTAL(3,$F$1:$F$50)-1) This works if only one criterion is selected in column D (e.g. Ireland). It counts the visible rows with data in the range F1:F50, and subtracts one for the heading. The number of rows in the table that meet the criterion in cell K1 is divided by this number. Public Function ShowFilter(rng As Range) 'UDF that displays the filter criteria. 'posted by Tom Ogilvy 1/17/02 'To make it respond to a filter change, tie it to the subtotal command. '=showfilter(B2)&CHAR(SUBTOTAL(9,B3)*0+32) 'So the above would show the criteria for column B Dim filt As Filter Dim sCrit1 As String Dim sCrit2 As String Dim sop As String Dim lngOp As Long Dim lngOff As Long Dim frng As Range Dim sh As Worksheet Set sh = rng.Parent If sh.FilterMode = False Then ShowFilter = "No Active Filter" Exit Function End If Set frng = sh.AutoFilter.Range If Intersect(rng.EntireColumn, frng) Is Nothing Then ShowFilter = CVErr(xlErrRef) Else lngOff = rng.Column - frng.Columns(1).Column + 1 If Not sh.AutoFilter.Filters(lngOff).On Then ShowFilter = "No Conditions" Else Set filt = sh.AutoFilter.Filters(lngOff) On Error Resume Next sCrit1 = filt.Criteria1 sCrit2 = filt.Criteria2 lngOp = filt.Operator If lngOp = xlAnd Then sop = " And " ElseIf lngOp = xlOr Then sop = " or " Else sop = "" End If ShowFilter = sCrit1 & sop & sCrit2 End If End If End Function -- Dave Peterson ec35720@msn.com |
| 5716 | If you add a Subtotal function to the worksheet, and refer to the first couple of cells in the sort range, it should trigger the calculate event when the list is sorted. For example, if the list is in A1:A100, enter the following formula in cell C1: =SUBTOTAL(3,A1:A2) Add the following code to the worksheet module: Private Sub Worksheet_Calculate() MsgBox "Calc" End Sub And the message will appear each time the list is sorted. I don't know how well this will work in your workbook, but it may give you some ideas. Lurch wrote: hi, Is there an event that gets triggered when I sort some columns in a sheet? I'm trying to write a macro, and connect it to a function that's executed when the user sorts some text on a worksheet, but I can't find one... I thought that maybe the calculate or the changed eventcode would execute, but it doesn't.. or am I missing something in the settings? Thanks in advance.. PS: Using Office 2000 Premium (Dutch version) -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 5749 | Hi Debra, The idea of adding in a "fake" calculation to force the _Calculate event to trigger is a nice touch. But the problem would be how to detect specifically that the sort event had been triggered, rather than just some random entry in a cell. What about a function that looks a little like this: Function StashContents(a_rng As Range) As Boolean Application.Volatile Static vStash As Variant Dim i As Long, j As Long StashContents = True If Not IsEmpty(vStash) Then For i = 1 To a_rng.Columns.Count For j = 1 To a_rng.Rows.Count If a_rng(j, i).Value < vStash(j, i) Then StashContents = False GoTo ExitPoint End If Next j Next i End If ExitPoint: vStash = a_rng.Value End Function Park it on the spreadsheet somewhere - I used cell F3 with a call something like =StashContents(A1:C10) if that's the range that might be sorted. And then in the worksheet calculate event you can use something like: Private Sub Worksheet_Calculate() If Me.Range("F3").Value = False Then Debug.Print "Range has been changed in some way" Else Debug.Print "Calculate - no change in the range" End If End Sub It seems a bit like overkill, but I don't know exactly what the OP's objective is. BTW if Lurch takes up this solution the function needs to be in a Module, not the Worksheet code sheet where the _Calculate event lives. Regards, Peter Beach "Debra Dalgleish" <dsd@contextures.com wrote in message news:3D66D1D8.5080205@contextures.com... If you add a Subtotal function to the worksheet, and refer to the first couple of cells in the sort range, it should trigger the calculate event when the list is sorted. For example, if the list is in A1:A100, enter the following formula in cell C1: =SUBTOTAL(3,A1:A2) Add the following code to the worksheet module: Private Sub Worksheet_Calculate() MsgBox "Calc" End Sub And the message will appear each time the list is sorted. I don't know how well this will work in your workbook, but it may give you some ideas. Lurch wrote: hi, Is there an event that gets triggered when I sort some columns in a sheet? I'm trying to write a macro, and connect it to a function that's executed when the user sorts some text on a worksheet, but I can't find one... I thought that maybe the calculate or the changed eventcode would execute, but it doesn't.. or am I missing something in the settings? Thanks in advance.. PS: Using Office 2000 Premium (Dutch version) -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 6058 | Glad I could help! Laura "Thomas P. Peacock" <peacockz@pacbell.net wrote in message news:lNj69.5018$l31.216200378@newssvr13.news.prodigy.com... Laura, thanks! That seem to do the trick; now I have somewhere to go to find out why this is happening. tpp "Laura Wilde" <lwilde@new.rr.com wrote in message news:#xTMcizQCHA.2404@tkmsftngp13... Excel is not seeing these numbers as true numbers. In a blank cell enter the number 1 Copy that cell. Highlight the cells of golf data Go to Edit Paste Special Under Operation, choose "Multiply" Click OK -- HTH, Laura (at home) "Thomas P. Peacock" <peacockz@pacbell.net wrote in message news:JCh69.4774$zl.213469470@newssvr13.news.prodigy.com... I have imported golf data from three separate days into Excel 2000. I then use DATA, SUBTOTALS to insert the correct formula to get team totals. However, the formula returns a value of "0". I have to reenter the cells above the formula to get the correct result. Any help??? thanks, tpp |
| 6405 | see answers in .worksheet.functions, and .charting Kurt K wrote: Hello and thanks in advance for any help. I have set up a pivot table to summarize estimates from a spread sheet. Personnel Hours Rate Subtotal Jeff 1 $100 $100 My pivot table then summarizes all the work for Jeff, is there a way to break apart a Personnel field, into individual fields see below. Personnel Hours Rate Subtotal Jeff 1 $100 $100 Jeff, Joe 1 $100 $100 Can I get my pivot table to show: Jeff 2 Hours Joe 1 Hour instead of: Jeff 1 hours Jeff, Joe 1 hour Thanks for your help. -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |