|
Debra Dalgleish
Dear Debra
I posted the following messages but no one answered, I thought you might have the answer. Please help. 1. When deleting calculated fields in my pivot table, Excel crashes. Plus when deleting a calculated field my pivot table result changed even when this calculated field is not part of the rest of the calculated fields. If I set it to zero there is no problem. Have you had this problem and know why this happening? 2. How can I use macro to create/ammend calculated field using the names and formula from a table on the spreadsheet? i.e A B C 1 Name Formula 2 Cost =Cost A+Cost B New 3 Revenue =Rev A+Rev B Existing Where the calculated field Cost is calculated field to be created and Revenue is an existing one requiring to be Modify |
Debra Dalgleish
Very confusing problem Debra... I suggest you make your problem more clear. Pleople can only reply to your problem if they understand what the problem is... Use a step by step description of what you did and give examples of cell values you have or want to have. Column headings, etc. -- Portuga ------------------------------------------------------------------------ Portuga's Profile: http://www.excelforum.com/member.php...fo&userid=6385 View this thread: http://www.excelforum.com/showthread...hreadid=540623 |
Debra Dalgleish
Portuga
Are you sure you know anything about pivottable and calculated fields???? "Portuga" wrote: Very confusing problem Debra... I suggest you make your problem more clear. Pleople can only reply to your problem if they understand what the problem is... Use a step by step description of what you did and give examples of cell values you have or want to have. Column headings, etc. -- Portuga ------------------------------------------------------------------------ Portuga's Profile: http://www.excelforum.com/member.php...fo&userid=6385 View this thread: http://www.excelforum.com/showthread...hreadid=540623 |
Debra Dalgleish
My excel skills are not the point here... I do know about Pivot tables, I work with it in a daily basis...but still dont get what you want to do or what your problem really is. This is your second post about this problem and no one replied to it. I suggest you work in the description of the problem... and maybe your manners to the people that are trying to help you. -- Portuga ------------------------------------------------------------------------ Portuga's Profile: http://www.excelforum.com/member.php...fo&userid=6385 View this thread: http://www.excelforum.com/showthread...hreadid=540623 |
Debra Dalgleish
1. What don't you understand about Excel crashing when I delete a calculated
field? 2. I have a redundant calculated field (i.e not being used) that when it is deleted is affecting the result of my pivot table. 3. I would like help with a macro to add/modify calculated field from list with their names and repective formula on the spreadsheet. "Portuga" wrote: My excel skills are not the point here... I do know about Pivot tables, I work with it in a daily basis...but still dont get what you want to do or what your problem really is. This is your second post about this problem and no one replied to it. I suggest you work in the description of the problem... and maybe your manners to the people that are trying to help you. -- Portuga ------------------------------------------------------------------------ Portuga's Profile: http://www.excelforum.com/member.php...fo&userid=6385 View this thread: http://www.excelforum.com/showthread...hreadid=540623 |
Debra Dalgleish
I think my excel skills are not enough comprehend your problem. 1- My excel never crashes when I delete any field, calculated or not from the *data that feeds in the pivot table*. You cant delete fields from the pivot itself. You can either select them or deselect them in the "Page", "RoW", "Column" or "data" sections of the pivot section 2- If you delete a field and and the results of your pivot are changed that means that the field is being used in some way. Otherwise there would be no changes in the Pivot. Right click on your pivot select "wizard..." select "Layout" see what fields are being used/or not all said ... it all makes sense when you have the spreadsheet in front of you but not like this. My suggestion then is to zip your spreadsheet and attach it in this forum. Maybe you will get more answers. -- Portuga ------------------------------------------------------------------------ Portuga's Profile: http://www.excelforum.com/member.php...fo&userid=6385 View this thread: http://www.excelforum.com/showthread...hreadid=540623 |
Debra Dalgleish
I'm not Debra, but if excel were crashing when I was doing something that seems
normal, I'd suspect a couple of things. #1. A corrupted workbook. I'd try building a smaller version of the same workbook (just enough data to test) and see what happens. If it worked in that test workbook, I'd rebuild the real version. #2. A corrupted installation of excel. Maybe help|detect and repair or reinstalling excel would help. In fact, I think I'd do some more detective work. Maybe test the workbook on a different pc to see what happens. Maybe it'll help isolate the error. nc wrote: Dear Debra I posted the following messages but no one answered, I thought you might have the answer. Please help. 1. When deleting calculated fields in my pivot table, Excel crashes. Plus when deleting a calculated field my pivot table result changed even when this calculated field is not part of the rest of the calculated fields. If I set it to zero there is no problem. Have you had this problem and know why this happening? 2. How can I use macro to create/ammend calculated field using the names and formula from a table on the spreadsheet? i.e A B C 1 Name Formula 2 Cost =Cost A+Cost B New 3 Revenue =Rev A+Rev B Existing Where the calculated field Cost is calculated field to be created and Revenue is an existing one requiring to be Modify -- Dave Peterson |
Debra Dalgleish
Dave's suggestions should help with question 1. For question 2, you
could record the steps as you create or modify the formulas manually. Then, substitute references to your list items. For example, with Cost and Revenue (A2:A3) in a range named AddItems: '-------------------- Sub CreateCalcFields() Dim rngItems As Range Dim ws As Worksheet Dim pt As PivotTable Dim c As Range Set ws = Worksheets("Pivot") Set pt = ws.PivotTables(1) Set rngItems = ws.Range("AddItems") For Each c In rngItems Select Case c.Offset(0, 2).Value Case "New" ws.PivotTables(1).CalculatedFields.Add _ c.Value, c.Offset(0, 1).Value, True pt.PivotFields(c.Value).Orientation = xlDataField Case "Existing" ws.PivotTables(1).CalculatedFields(c.Value) _ .StandardFormula = c.Offset(0, 1).Value Case Else 'do nothing End Select Next c ' End Sub '---------------------- nc wrote: Dear Debra I posted the following messages but no one answered, I thought you might have the answer. Please help. 1. When deleting calculated fields in my pivot table, Excel crashes. Plus when deleting a calculated field my pivot table result changed even when this calculated field is not part of the rest of the calculated fields. If I set it to zero there is no problem. Have you had this problem and know why this happening? 2. How can I use macro to create/ammend calculated field using the names and formula from a table on the spreadsheet? i.e A B C 1 Name Formula 2 Cost =Cost A+Cost B New 3 Revenue =Rev A+Rev B Existing Where the calculated field Cost is calculated field to be created and Revenue is an existing one requiring to be Modify -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Debra Dalgleish
Dear Debra
Thanks for your reply. For Question 1. When I set the value of the redundant calculated field's formula to zero there is no effect on the pivot table result. When I do manual calculation i.e not using pivot table I get the same result as before the deletion. I am sure the deleted calculated field is not being used searching the listed calculated fields after deleting it. 2. Instead of having existing and new in a column, can the macro be ammended get the information from Excel, then Add/modify based on this information. "Debra Dalgleish" wrote: Dave's suggestions should help with question 1. For question 2, you could record the steps as you create or modify the formulas manually. Then, substitute references to your list items. For example, with Cost and Revenue (A2:A3) in a range named AddItems: '-------------------- Sub CreateCalcFields() Dim rngItems As Range Dim ws As Worksheet Dim pt As PivotTable Dim c As Range Set ws = Worksheets("Pivot") Set pt = ws.PivotTables(1) Set rngItems = ws.Range("AddItems") For Each c In rngItems Select Case c.Offset(0, 2).Value Case "New" ws.PivotTables(1).CalculatedFields.Add _ c.Value, c.Offset(0, 1).Value, True pt.PivotFields(c.Value).Orientation = xlDataField Case "Existing" ws.PivotTables(1).CalculatedFields(c.Value) _ .StandardFormula = c.Offset(0, 1).Value Case Else 'do nothing End Select Next c ' End Sub '---------------------- nc wrote: Dear Debra I posted the following messages but no one answered, I thought you might have the answer. Please help. 1. When deleting calculated fields in my pivot table, Excel crashes. Plus when deleting a calculated field my pivot table result changed even when this calculated field is not part of the rest of the calculated fields. If I set it to zero there is no problem. Have you had this problem and know why this happening? 2. How can I use macro to create/ammend calculated field using the names and formula from a table on the spreadsheet? i.e A B C 1 Name Formula 2 Cost =Cost A+Cost B New 3 Revenue =Rev A+Rev B Existing Where the calculated field Cost is calculated field to be created and Revenue is an existing one requiring to be Modify -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Debra Dalgleish
Thanks for your reply.
How can I attach zip files to the forum? I am sure that the deleted calculated field is not being used. (See my reply to Debra) "Portuga" wrote: I think my excel skills are not enough comprehend your problem. 1- My excel never crashes when I delete any field, calculated or not from the *data that feeds in the pivot table*. You cant delete fields from the pivot itself. You can either select them or deselect them in the "Page", "RoW", "Column" or "data" sections of the pivot section 2- If you delete a field and and the results of your pivot are changed that means that the field is being used in some way. Otherwise there would be no changes in the Pivot. Right click on your pivot select "wizard..." select "Layout" see what fields are being used/or not all said ... it all makes sense when you have the spreadsheet in front of you but not like this. My suggestion then is to zip your spreadsheet and attach it in this forum. Maybe you will get more answers. -- Portuga ------------------------------------------------------------------------ Portuga's Profile: http://www.excelforum.com/member.php...fo&userid=6385 View this thread: http://www.excelforum.com/showthread...hreadid=540623 |
Debra Dalgleish
Thanks Dave for your reply.
I tried help|detect and repair. But it did not solve the problem. This is the repair log, Repairs were made to PivotTable report 'PivotTable9' on '[TAS summary 2005-06 II.xls]FAH'. Global PivotTable report information was repaired due to integrity problems. Global PivotTable report information was repaired due to integrity problems. Global PivotTable report information was repaired due to integrity problems. Global PivotTable report information was repaired due to integrity problems. Global PivotTable report information was repaired due to integrity problems. Global PivotTable report information was repaired due to integrity problems. Global PivotTable report information was repaired due to integrity problems. Global PivotTable report information was repaired due to integrity problems. Global PivotTable report information was repaired due to integrity problems. Global PivotTable report information was repaired due to integrity problems. Global PivotTable report information was repaired due to integrity problems. My main concern, there is no reason for the result of the pivottable changing when I delete a calculated field that is not being used anywhere in the pivottable or part of another calculated field. I can confirm the fact because I have tool|find to search the list of calculated fileds. "Dave Peterson" wrote: I'm not Debra, but if excel were crashing when I was doing something that seems normal, I'd suspect a couple of things. #1. A corrupted workbook. I'd try building a smaller version of the same workbook (just enough data to test) and see what happens. If it worked in that test workbook, I'd rebuild the real version. #2. A corrupted installation of excel. Maybe help|detect and repair or reinstalling excel would help. In fact, I think I'd do some more detective work. Maybe test the workbook on a different pc to see what happens. Maybe it'll help isolate the error. nc wrote: Dear Debra I posted the following messages but no one answered, I thought you might have the answer. Please help. 1. When deleting calculated fields in my pivot table, Excel crashes. Plus when deleting a calculated field my pivot table result changed even when this calculated field is not part of the rest of the calculated fields. If I set it to zero there is no problem. Have you had this problem and know why this happening? 2. How can I use macro to create/ammend calculated field using the names and formula from a table on the spreadsheet? i.e A B C 1 Name Formula 2 Cost =Cost A+Cost B New 3 Revenue =Rev A+Rev B Existing Where the calculated field Cost is calculated field to be created and Revenue is an existing one requiring to be Modify -- Dave Peterson |
Debra Dalgleish
Please don't suggest that other users attach files to this forum. Most
people will not bother to open them, or even read a post with an attachment. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ "Portuga" wrote in message ... I think my excel skills are not enough comprehend your problem. 1- My excel never crashes when I delete any field, calculated or not from the *data that feeds in the pivot table*. You cant delete fields from the pivot itself. You can either select them or deselect them in the "Page", "RoW", "Column" or "data" sections of the pivot section 2- If you delete a field and and the results of your pivot are changed that means that the field is being used in some way. Otherwise there would be no changes in the Pivot. Right click on your pivot select "wizard..." select "Layout" see what fields are being used/or not all said ... it all makes sense when you have the spreadsheet in front of you but not like this. My suggestion then is to zip your spreadsheet and attach it in this forum. Maybe you will get more answers. -- Portuga |
Debra Dalgleish
What happened when you built the smaller workbook and ran the test?
nc wrote: Thanks Dave for your reply. I tried help|detect and repair. But it did not solve the problem. This is the repair log, Repairs were made to PivotTable report 'PivotTable9' on '[TAS summary 2005-06 II.xls]FAH'. Global PivotTable report information was repaired due to integrity problems. Global PivotTable report information was repaired due to integrity problems. Global PivotTable report information was repaired due to integrity problems. Global PivotTable report information was repaired due to integrity problems. Global PivotTable report information was repaired due to integrity problems. Global PivotTable report information was repaired due to integrity problems. Global PivotTable report information was repaired due to integrity problems. Global PivotTable report information was repaired due to integrity problems. Global PivotTable report information was repaired due to integrity problems. Global PivotTable report information was repaired due to integrity problems. Global PivotTable report information was repaired due to integrity problems. My main concern, there is no reason for the result of the pivottable changing when I delete a calculated field that is not being used anywhere in the pivottable or part of another calculated field. I can confirm the fact because I have tool|find to search the list of calculated fileds. "Dave Peterson" wrote: I'm not Debra, but if excel were crashing when I was doing something that seems normal, I'd suspect a couple of things. #1. A corrupted workbook. I'd try building a smaller version of the same workbook (just enough data to test) and see what happens. If it worked in that test workbook, I'd rebuild the real version. #2. A corrupted installation of excel. Maybe help|detect and repair or reinstalling excel would help. In fact, I think I'd do some more detective work. Maybe test the workbook on a different pc to see what happens. Maybe it'll help isolate the error. nc wrote: Dear Debra I posted the following messages but no one answered, I thought you might have the answer. Please help. 1. When deleting calculated fields in my pivot table, Excel crashes. Plus when deleting a calculated field my pivot table result changed even when this calculated field is not part of the rest of the calculated fields. If I set it to zero there is no problem. Have you had this problem and know why this happening? 2. How can I use macro to create/ammend calculated field using the names and formula from a table on the spreadsheet? i.e A B C 1 Name Formula 2 Cost =Cost A+Cost B New 3 Revenue =Rev A+Rev B Existing Where the calculated field Cost is calculated field to be created and Revenue is an existing one requiring to be Modify -- Dave Peterson -- Dave Peterson |
Debra Dalgleish
To have Excel test for the calculated fields, you could use something
like this: '---------------------- Sub CreateCalcFields() Dim rngItems As Range Dim ws As Worksheet Dim pt As PivotTable Dim c As Range Dim pf As PivotField Set ws = Worksheets("Pivot") Set pt = ws.PivotTables(1) Set rngItems = ws.Range("AddItems") For Each c In rngItems On Error Resume Next Set pf = ws.PivotTables(1).CalculatedFields(c.Value) On Error GoTo 0 If Not pf Is Nothing Then ws.PivotTables(1).CalculatedFields(c.Value) _ .StandardFormula = c.Offset(0, 1).Value Else ws.PivotTables(1).CalculatedFields.Add _ c.Value, c.Offset(0, 1).Value, True pt.PivotFields(c.Value).Orientation = xlDataField End If Next c End Sub '-------------------------- nc wrote: Dear Debra Thanks for your reply. For Question 1. When I set the value of the redundant calculated field's formula to zero there is no effect on the pivot table result. When I do manual calculation i.e not using pivot table I get the same result as before the deletion. I am sure the deleted calculated field is not being used searching the listed calculated fields after deleting it. 2. Instead of having existing and new in a column, can the macro be ammended get the information from Excel, then Add/modify based on this information. "Debra Dalgleish" wrote: Dave's suggestions should help with question 1. For question 2, you could record the steps as you create or modify the formulas manually. Then, substitute references to your list items. For example, with Cost and Revenue (A2:A3) in a range named AddItems: '-------------------- Sub CreateCalcFields() Dim rngItems As Range Dim ws As Worksheet Dim pt As PivotTable Dim c As Range Set ws = Worksheets("Pivot") Set pt = ws.PivotTables(1) Set rngItems = ws.Range("AddItems") For Each c In rngItems Select Case c.Offset(0, 2).Value Case "New" ws.PivotTables(1).CalculatedFields.Add _ c.Value, c.Offset(0, 1).Value, True pt.PivotFields(c.Value).Orientation = xlDataField Case "Existing" ws.PivotTables(1).CalculatedFields(c.Value) _ .StandardFormula = c.Offset(0, 1).Value Case Else 'do nothing End Select Next c ' End Sub '---------------------- nc wrote: Dear Debra I posted the following messages but no one answered, I thought you might have the answer. Please help. 1. When deleting calculated fields in my pivot table, Excel crashes. Plus when deleting a calculated field my pivot table result changed even when this calculated field is not part of the rest of the calculated fields. If I set it to zero there is no problem. Have you had this problem and know why this happening? 2. How can I use macro to create/ammend calculated field using the names and formula from a table on the spreadsheet? i.e A B C 1 Name Formula 2 Cost =Cost A+Cost B New 3 Revenue =Rev A+Rev B Existing Where the calculated field Cost is calculated field to be created and Revenue is an existing one requiring to be Modify -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Debra Dalgleish
Hi Debra
Thanks for your reply. I have tried the macro I am getting the following error message box "Run-time error '7' Out of Memory". I have set up the AddItems name with range =Sheet1!$D$3:$E$13 The pivot table and the above range are on "Sheet 1" Recorded the following macro, Sub Macro3() ' ' Macro3 Macro ' Macro recorded 12/05/2006 by Authorised User ActiveSheet.PivotTables("PivotTable1").CalculatedF ields.Add "A1P3 Error", "= 0" _ , True ActiveSheet.PivotTables("PivotTable1").PivotFields ("A1P3 Error").Orientation = _ xlDataField ActiveSheet.PivotTables("PivotTable1").CalculatedF ields("A1P3 Error"). _ StandardFormula = "= 2" End Sub Then made changes to your macro Sub CreateCalcFields() Dim rngItems As Range Dim ws As Worksheet Dim pt As PivotTable Dim c As Range Dim pf As PivotField Set ws = Worksheets("Sheet1") Set pt = ws.PivotTables("PivotTable1") Set rngItems = ws.Range("AddItems") For Each c In rngItems On Error Resume Next Set pf = ws.PivotTables("PivotTable1").CalculatedFields(c.V alue) On Error GoTo 0 If Not pf Is Nothing Then ws.PivotTables("PivotTable1").CalculatedFields(c.V alue) _ .StandardFormula = c.Offset(0, 1).Value Else ws.PivotTables("PivotTable1").CalculatedFields.Add _ c.Value, c.Offset(0, 1).Value, True pt.PivotFields(c.Value).Orientation = xlDataField End If Next c End Sub "Debra Dalgleish" wrote: To have Excel test for the calculated fields, you could use something like this: '---------------------- Sub CreateCalcFields() Dim rngItems As Range Dim ws As Worksheet Dim pt As PivotTable Dim c As Range Dim pf As PivotField Set ws = Worksheets("Pivot") Set pt = ws.PivotTables(1) Set rngItems = ws.Range("AddItems") For Each c In rngItems On Error Resume Next Set pf = ws.PivotTables(1).CalculatedFields(c.Value) On Error GoTo 0 If Not pf Is Nothing Then ws.PivotTables(1).CalculatedFields(c.Value) _ .StandardFormula = c.Offset(0, 1).Value Else ws.PivotTables(1).CalculatedFields.Add _ c.Value, c.Offset(0, 1).Value, True pt.PivotFields(c.Value).Orientation = xlDataField End If Next c End Sub '-------------------------- nc wrote: Dear Debra Thanks for your reply. For Question 1. When I set the value of the redundant calculated field's formula to zero there is no effect on the pivot table result. When I do manual calculation i.e not using pivot table I get the same result as before the deletion. I am sure the deleted calculated field is not being used searching the listed calculated fields after deleting it. 2. Instead of having existing and new in a column, can the macro be ammended get the information from Excel, then Add/modify based on this information. "Debra Dalgleish" wrote: Dave's suggestions should help with question 1. For question 2, you could record the steps as you create or modify the formulas manually. Then, substitute references to your list items. For example, with Cost and Revenue (A2:A3) in a range named AddItems: '-------------------- Sub CreateCalcFields() Dim rngItems As Range Dim ws As Worksheet Dim pt As PivotTable Dim c As Range Set ws = Worksheets("Pivot") Set pt = ws.PivotTables(1) Set rngItems = ws.Range("AddItems") For Each c In rngItems Select Case c.Offset(0, 2).Value Case "New" ws.PivotTables(1).CalculatedFields.Add _ c.Value, c.Offset(0, 1).Value, True pt.PivotFields(c.Value).Orientation = xlDataField Case "Existing" ws.PivotTables(1).CalculatedFields(c.Value) _ .StandardFormula = c.Offset(0, 1).Value Case Else 'do nothing End Select Next c ' End Sub '---------------------- nc wrote: Dear Debra I posted the following messages but no one answered, I thought you might have the answer. Please help. 1. When deleting calculated fields in my pivot table, Excel crashes. Plus when deleting a calculated field my pivot table result changed even when this calculated field is not part of the rest of the calculated fields. If I set it to zero there is no problem. Have you had this problem and know why this happening? 2. How can I use macro to create/ammend calculated field using the names and formula from a table on the spreadsheet? i.e A B C 1 Name Formula 2 Cost =Cost A+Cost B New 3 Revenue =Rev A+Rev B Existing Where the calculated field Cost is calculated field to be created and Revenue is an existing one requiring to be Modify -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
| All times are GMT +1. The time now is 10:16 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com