MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Pivot tables - calculated field


Posted by Rob on May 08, 2001 2:23 PM

I have created a pivot table in an Excel worksheet that is connected directly (OLAP) to a Sequel server. I am trying to create calculated fields in the same manner I use when the data is located in a worksheet, but it will not allow me to do this. The "calculated fields" option is grayed out. Does anyone have an idea how this can be done, if at all?

Thanks very much.


Posted by Mark W. on May 08, 2001 2:29 PM

Where in the PivotTable is the active cell?

Posted by Rob on May 09, 2001 7:26 AM

I'm sorry, I don't know what you mean.

Posted by Mark W. on May 09, 2001 7:31 AM

The active cell is the selected cell displayed
in the Name Box. What part (e.g., page field,
row field item, data area, etc.) of the PivotTable
contains the active cell?

Posted by Rob on May 09, 2001 8:57 AM

OK, got it. The active cell in this case is in the data area.

Posted by Mark W. on May 09, 2001 10:43 AM

When you say that Calculated Field... is grayed out
are you referring to the PivotTable Toolbar or the
Excel menu? I trust that you haven't protected your
worksheet. Right? OK, got it. The active cell in this case is in the data area. : The active cell is the selected cell displayed

Posted by Mark on May 09, 2001 10:49 AM

I ran into a similar issue using an ODBC link. I also had the issue with how excel totals calculated fields. My workaround was to create my calcluated field in excel, then use ODBC to connect to an access database. Killed two birds with one stone. I don't know if it's the optimal solution but it worked for me.

Posted by Rob on May 10, 2001 6:11 AM

I am referring to the pivot table toolbar. I right-click on the table, select "Formulas" from the menu, which gives 4 options. One of these options is calculated field, which in this case is grayed out.
I have not protected my worksheet. When you say that Calculated Field... is grayed out