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 |
| 174 | I've done many compound graphs like you describe. The trick is to create separate charts embedded on a worksheet. By careful use of formatting, alignment by snapping to cell intersections and grouping, I manage to get the desired effect. In your oscilloscope analogy, you would have 3 differing Y axis and want to share the X or Time axis. In other words, only the bottom time scale shows. I often do this by scaling all X axes identically on all charts then overlapping/hiding the axis for the top graphs. You might think you can just delete the unwanted axis, but the problem you will continually do battle with is Excel's distressing tendency to resize the plot area every time you alter axis formatting. You will want everything *exactly* the same except the Y axis and perhaps, the series displayed. Hint - no auto scaling/formatting whatsoever. No auto *anything*. Basically the procedure is this: 1) Create the bottom chart exactly as you would like to see it. 2) Fix (freeze) the axes by turning autoscaling off. 3) Copy/paste this chart for as many charts as you are going to stack. 4) Alter the Y scaling on the stacked charts as necessary. 5) Lay them out and glue them together (group) With a little effort, the results can be perfect. Thomas Bartkus "Microsoft" <edward@biebel.net wrote in message news:u8btS6uFCHA.2324@tkmsftngp04... Hi all: I have a question that has got me stumped and which I believe cannot done in Excel but figured I'd ask to be sure. I've been asked to construct a chart that has three different scales on it. However, the problem is not the multiple scales but the layout that is being requested. Essentially the person requesting the chart wants the bottom third of the chart done in one scale, the middle third done in a second scale and the top third in third scale. The only analogy that I can think of to describe the look he would like if from my HS physics days where you have an oscilloscope with different meter bands each with its own waveform stacked on top of each other. Anyone ever tried this and does this even sound possible? It didn't possible to me but you known how it goes when you get the "this should be relatively simple" requests. Thanks, Ed |
| 492 | What version of Excel are you using? What do you see when you choose the Group command? Some of your dates may be missing, or they may be entered as text, instead of dates. Either of these would cause problems with grouping. Philip wrote: I am trying to group dates in the source data of my pivot table to "read" in "Qtr" etc. I have followed the instructions on the help menu and the sample pivot tables, but I can not figure it out. When I go to the group menu, it does not "pull" up the sub menu for "date" listing. All help would be appreciated Philip -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 765 | Hi Maurice Got to Tools=Options=Custom Lists and create a new custom List Sep Oct Nov etc. through to Aug In the Pivot Table, double click on the field heading, click on Advanced and set Autosort to Manual using your date field. This should get the results in the correct order -- Roger Govier Technology 4 U W98SR2 XL2K On Mon, 24 Jun 2002 03:11:17 -0700, "Maurice O omen" <moomen@planet.nl wrote: Thanks, and sorry for my late reply. The format of the dates in the fields are actually dd-mmm-yy, so 9-Oct-01, 12-May-02, etc. And grouping months as such goes fine, it is just that it puts January 2002 before October 2001, so it says "Jan", "Feb", etc. till "Dec", appearantly ignoring the years. Maurice -----Original Message----- Maurice, Do you have actual dates? You indicate Sept-01, which is a date in Excel's book. Get it? Book. <g Oh well. Though it's September first to Excel, not Sep 2001. Then you indicate Sep(02), which isn't a date for sure. It sounds as if you do not. I think you need real dates. You can type them in as the first of their months (10/01/01). That will cause all of each month to be in a single group in the pivot table. To get them to appear as Sep(01), format them (Format, Number, Custom) as mmm(yy). If you already have actual complete dates, then make a column for first-of-month dates, as follows: =DATE(YEAR(B2),MONTH(B2),1) This converts the dates to the first of their months, so they'll group by month. Format as above. Use it in your pivot table. Regards from Virginia Beach, EarlK ---------------------------------------------------------- --- "Maurice Oomen" <moomen@planet.nl wrote in message news:ad0lhs$9fp$1@reader11.wxs.nl... I have created a pivot table with a time dimension having dates ranging from Sept-01 to Apr-02 (today). When I group these dates into months, Excel sorts the months wrong. I.e. it is not taking the years into account. It sorts like: Jan, Feb, Mar, Apr, Sep, Oct, Nov, Dec. Whereas it should sort like: Sep(01), Oct(01), Nov(01), Dec(01), Jan(02), Feb(02), Mar(02), Apr(02). Is this a bug? Or am I doing something wrong? Thx, Maurice -------------------------------------------------------- ------------------ -- ---- . |
| 1074 | Thanks Debra, this worked. It was being quite tempremental though. I had also done some grouping and it didn't like me trying to create a calculated item field. I have another issue now though. I have two levels of row data in my pivot table. The outer row is a sales region and the second level is a district. Right now every district is being listed under each region. I only want the districts that contain data to show up for each district. How do I do this? Here is an example: West Northeast Central Pacific 2000 Southwest 1000 East Northeast 5000 Central Pacific Southwest I want: West Pacific 2000 Southwest 1000 East Northeast 5000 -----Original Message----- 1. Select the Document Type button in the Pivot Table. 2. On the Pivot toolbar, choose PivotTableFormualasCalculated Item. 3. Type a name for the new Item, e.g. Returns (you can't use an existing field name) 4. Enter a formula, e.g.: =Return*-1 5. Click OK 6. Remove the original item (Return) from the Pivot Table. Jason L. wrote: There is not a field for "Returns". The field is "Document Type", so returns & invoices are items within this field. Here is an example: Doc. Type Amount Invoice 5000 Return 500 Invoice 10000 What I really want is the following formula: =if(Doc. Type="Return",-Amount,Amount) to get the following result, but I can't figure out how to do this in a pivot table. Doc. Type Amount Invoice 5000 Return (500) Invoice 10000 -----Original Message----- Hi Jason Select the pivotfield "Returns" on your pivot table. From the Pivot toolbar, select FormulasCalculated Field In the Name box enter something like "Negative Returns" In the formula box enter "=-" (no inverted commas) Double click on "Returns" in the Field Box Select AddOK Back in the Pivot table, select the arrow at the top of the data field and remove the check box from Returns. HTH -- Regards William willwest22@yahoo.com "Jason L" <jason.loesch@zinpro.com wrote in message news:1374501c21eba$0cde70d0$19ef2ecf@tkmsftngxa01... | I have a pivot table consisting of a field for document | type and a field for document amount. There are two | document types: invoices and returns. The problem is | that the source data is from an external source and the | document amounts for both types come in as positive | numbers. Can I create a calculated field that shows | invoices as positive and returns as negative? . -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html . |
| 1082 | Double-click on the District field button. Remove the check mark from 'Show items with no data' Jason L wrote: Thanks Debra, this worked. It was being quite tempremental though. I had also done some grouping and it didn't like me trying to create a calculated item field. I have another issue now though. I have two levels of row data in my pivot table. The outer row is a sales region and the second level is a district. Right now every district is being listed under each region. I only want the districts that contain data to show up for each district. How do I do this? Here is an example: West Northeast Central Pacific 2000 Southwest 1000 East Northeast 5000 Central Pacific Southwest I want: West Pacific 2000 Southwest 1000 East Northeast 5000 -----Original Message----- 1. Select the Document Type button in the Pivot Table. 2. On the Pivot toolbar, choose PivotTableFormualasCalculated Item. 3. Type a name for the new Item, e.g. Returns (you can't use an existing field name) 4. Enter a formula, e.g.: =Return*-1 5. Click OK 6. Remove the original item (Return) from the Pivot Table. Jason L. wrote: There is not a field for "Returns". The field is "Document Type", so returns & invoices are items within this field. Here is an example: Doc. Type Amount Invoice 5000 Return 500 Invoice 10000 What I really want is the following formula: =if(Doc. Type="Return",-Amount,Amount) to get the following result, but I can't figure out how to do this in a pivot table. Doc. Type Amount Invoice 5000 Return (500) Invoice 10000 -----Original Message----- Hi Jason Select the pivotfield "Returns" on your pivot table. From the Pivot toolbar, select FormulasCalculated Field In the Name box enter something like "Negative Returns" In the formula box enter "=-" (no inverted commas) Double click on "Returns" in the Field Box Select AddOK Back in the Pivot table, select the arrow at the top of the data field and remove the check box from Returns. HTH -- Regards William willwest22@yahoo.com "Jason L" <jason.loesch@zinpro.com wrote in message news:1374501c21eba$0cde70d0$19ef2ecf@tkmsftngxa01... | I have a pivot table consisting of a field for document | type and a field for document amount. There are two | document types: invoices and returns. The problem is | that the source data is from an external source and the | document amounts for both types come in as positive | numbers. Can I create a calculated field that shows | invoices as positive and returns as negative? . -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html . -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 2097 | Mike, If you need to use a Forms control button you can create the coloured effect by grouping a textbox over the button. You could also use an autoshaped Bevel to create a psuedo button. I have worked examples of these, if you are interested email me and I will send you. Cheers Andy "Mike" <dmcsoco@mongol.net wrote in message news:1537c01c228ee$99ac0430$36ef2ecf@tkmsftngxa12... I'm using Excel 2000. When I insert a control button from the Forms Toolbar to run a macro in a spreadsheet is there any way that I can change the background color of the control button? Many thanks for help and suggestions, Mike. |
| 2240 | It's not just grouping Jan 2002 dates, it's grouping January for all years in the database (even if you have just one year of data). You can group by Year and Month, and you'll have two date fields in the Column area, but the month field will still only show the Month Name, not mmm yyyy stuart noble wrote: Can anyone tell me why, in a pivot table, ungrouped dates display as 1st Jan 2002 but, when they're grouped by month, this is shortened to Jan Is there a way to to display Jan 2002 in the row headings? I can format the data itself, but not the row headings. Any help appreciated. -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 3037 | Hi Salman To select multiple sheets Click on first sheet required, hold down shift button then click on last sheet. The tabs of all the selected sheets will turn white. Anything entered onto any one sheet, will now automatically be added to the same location on all others. Single click on any sheet tab to deselect the grouping. -- Regards, Roger Govier Technology 4 U "salman" <sahmadmit@hotmail.com wrote in message news:07c101c2331b$e824bad0$9be62ecf@tkmsftngxa03... How can I copy a fmla (say =A2+B2) from Sheet1 and paste it to many sheets say Sheet2 to 16 in one go? Also how do I select all of sheet 1-16? (Without hitting ctrl+A on each sheet) Thank you. |
| 3575 | Try grouping all your defined names under one name. For example: Name Range one =Sheet1!$C$4:$D$19 two =Sheet1!$G$16,Sheet1!$H$18,Sheet1!$I$21 three =Sheet1!$G$2:$N$4,Sheet1!$K$5,Sheet1!$L$5 four =one,two,three Go to the Name Box and type in "four" (no quotes) and press enter to select all the cells. HTH Jason Atlanta, GA -----Original Message----- Hi folks Excel XP, W2K, 256 Mb RAM, 400 Mhz cpu I need to select a number of groups of cells in one go by naming them. I seem to hit a limit when doing the Ctrl/click/drag process. Does anyone know if there is a limit and if so what it is. Even better, does anyone know of a way round it? TIA Mike Boardman . |
| 3577 | I forgot to say that you should break your currently defined names into smaller groups, then group them all under 1 name. Jason -----Original Message----- Try grouping all your defined names under one name. For example: Name Range one =Sheet1!$C$4:$D$19 two =Sheet1!$G$16,Sheet1!$H$18,Sheet1!$I$21 three =Sheet1!$G$2:$N$4,Sheet1!$K$5,Sheet1!$L$5 four =one,two,three Go to the Name Box and type in "four" (no quotes) and press enter to select all the cells. HTH Jason Atlanta, GA -----Original Message----- Hi folks Excel XP, W2K, 256 Mb RAM, 400 Mhz cpu I need to select a number of groups of cells in one go by naming them. I seem to hit a limit when doing the Ctrl/click/drag process. Does anyone know if there is a limit and if so what it is. Even better, does anyone know of a way round it? TIA Mike Boardman . . |
| 5001 | 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 |
| 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 |
| 5238 | Hi everyone, I would like to know if there is any way one can sort data in excel as described below. I have a massive amount of data to sort and possibly group by reference number in a particular coloum. I manage to sort all the data in ascending order by this particular coloum and like this all reference numbers are sorted numerically but what I need to do is make Excel enter a blank row whenever the reference changes. I don't know if I made myself clear enough. I would like Excel to group all the rows with the same reference number one after the other THEN skip a row and carry on grouping with the next reference number. Any help? I need to sort close to a thousand rows of data and unless Excel can help me I'll have to do it manually!!!! Thanks to all out there.... Example of Raw Data: 36322 (20.00) 36332 20.00 36335 20.00 36335 (20.00) 36350 20.00 36350 (20.00) 36354 10.00 36355 (10.00) How I wish it to be after sorting and grouping: 36322 (20.00) 36332 20.00 36335 20.00 36335 (20.00) 36350 20.00 36350 (20.00) 36354 10.00 36355 (10.00) THANKS AGAIN TO ANYONE WHO WILL HELP ME. |
| 6291 | Hi everyone ! I want to protect some cell in worksheet ,And my worksheet have Data grouping. If I do protect sheet for Object. I shell can protect data entry in cell, But I can't Drill-Down Data Grouping. I want to protect some cell for data entry And Drill-Down Data Grouping. Help me ! Please |
| 6299 | All cells are locked/protected by default, so first you have to unlock them all by :- 1. click the grey box at the top left of the worksheet(where row & column names join) to select all the cells. 2. Format/Cells/Protection and *uncheck* "Locked". 3. Select the individual cells you want to protect. 4. Format/Cells/Protection and *check* "Locked". Hope this helps. Regards BrianB ======================================================== "Kumphon Rungphetcharat" <kumphon_l@yahoo.com wrote in message news:<a64c01c24fcd$df1a54d0$3bef2ecf@TKMSFTNGXA10... Hi everyone ! I want to protect some cell in worksheet ,And my worksheet have Data grouping. If I do protect sheet for Object. I shell can protect data entry in cell, But I can't Drill-Down Data Grouping. I want to protect some cell for data entry And Drill-Down Data Grouping. Help me ! Please |
| 6456 | In most cases Excel considers your regional settings in what it displays in the Format, Number dialog. If you do not see the format you want to use you have to create a custom number format. For example here's one for rupees where the digit grouping is by twos rather than threes: [9999999]"Rs."##\,##\,##\,##0.00;[99999]"Rs."##\,##\,##0.00;"Rs."#,###,0 00 -- Jim Rech Excel MVP |