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

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

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

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

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

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

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'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

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

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

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

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

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

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

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