Can't edit a named range in Excel 2007

[megnin]

I have a pivot table using a named range as the data source. I had to add another column to the data, so now my named range needs to be expanded.
In "Formulas" | "Name Manager" this "Name" has an icon that looks like a little table. I have other "Names" and they all have icons that look like little name tags. I can edit the ranges in the ones that look like name tags, but the range is greyed out in the one I need to edit, with the icon that looks like a table.
I don't know how I created this one (it's been a while). I created a new named range with the range I need and it looks like the others.
The reason I need to edit this one instead of just using the new one is when I change the range from the old one to the new one it destroys my carfully crafted pivot table. All the correct fields are there, of course, but none are selected so I'd basically have to start over.
Anyway, why does the one have a different icon and why can't I edit it's range?

 

[xenou]

It may be an internal table used by the pivot table (?). I can find no reason why table names cannot be edited - although in some odd way it seems that tables shouldn't need to be edited (they should expand and contract with the table itself - I see your point though - you want a new column out there on the right I presume).

You could try this trick: insert your new column Inside the existing data table, rather than placing it in an empty column to the left. Now it should be in your named range and the range will expand automatically. If you like, cut and copy the column from the right edge back into the table again to the left of the new column, which now becomes the new right edge.

Does that work?

Note: I've not become a fan of Excel tables myself yet - I've never had problems with normal ranges - at least, I'd prefer they not show up unless asked for (by me). But I've seen some enthusiastic posts about them so I'm trying to get up to speed.

 

[megnin]

Hmmmm. I tried as you suggested, to cut that last column which is outside the table and insert it inside the table and I got a message informing me that, "This operation is not allowed. The operation is attempting to shift cells in a table on your worksheet."

Darn. I had hoped that was the answer.

I think I may just have to take a screen shot of the configuration of the pivot table(s) and just rebuild it(them).

 

[xenou]

Rat. I had hoped so too. If you find any answer let us know - it sounds like something that would affect others (if you're data isn't confidential I'd be glad to take a look - I'll PM you if you still have a copy of the bugger that could be sent over to me).

ξ

 

[megnin]

There's nothing any more confidential in the data than a handful of first and last names. I'd be happy to send you the Excel file to you but it's just over 15MB.
The "Data" tab has 15 columns and 40,000+ rows. The first 14 columns are pulled from an external datasource (SQL Server 2005) and the last column is calculated from some of the others (SUMed).

Two points I just thought of... #2 first: I should have realized I could not cut and insert a calculated column in the middle of a table pulled from an external data source.
#1: On the second tab "PivotTable" where the five or six pivot tables are, each of the pivot tables are already configred for the correct number of columns. I was looking at the first pivot table on the sheet which does not need the last column so it's not part of it's datasource. The pivot tables that need the last column already have it configured in. (Sorry, I haven't worked on this thing in almost a year and I'm trying to do some updates on it now.)

Okay, so those were "brain farts", but I do have an issue with the pivot tables now that's kicking my butt.

I have a field in the "Values" area of the pivot table called "f_Percent" and I can't remember where that came from. It has valid values in it, but when I refresh the pivot table they all change to #NAME? and if I try to unselect that field in the "Choose fields to add to report:" area of the PivotTable Field List it says the PivotTable report is invalid. Try refreshing the data. I do and my values go away (#NAME?).
There is no column in the data named "f_Percent", it seems to be a calculated field, but I don't even remember how I created that field. Dang, I can't update the workbook until I fix it.
Do you know how to add a calculated field to a Pivot Table? THat might spark something in my memory.

 

[xenou]

In Excel 2007

• Click the field that contains the calculated item.
• On the Options tab, in the Tools group, click Formulas, and then click Calculated Item.
• In the Name box, select the calculated item.
• In the Formula box, edit the formula.
• Click Modify.

<img alt="calculated field" src="http://northernocean.net/etc/mrexcel/20100401_calculatedfield.png" />

 

[megnin]

Thanks. That tells me how to check/fix the pivot table calculated field if I have to build the pivot table again from scratch. I'm trying to avoid having to rebuild it, but now that I have this information it shouldn't be as hard.
I'm still unable to add that new column into the named range.
There has always also been a calculated column on the Data tab after the database connected columns, but since I added another database connected field the range has expanded by one column, so the named range now includes only the database connected columns and does not cover that last calculated column.
Under PivotTable Tools | Options | Change Data Source it shows the named range.
Under Formulas | Name Manager it shows the named range, but when I go to edit it, the range in "Refers to:" is greyed out so I'm unable to expand the range by changing column "N" to column "O".

In Excel 2007

• Click the field that contains the calculated item.
• On the Options tab, in the Tools group, click Formulas, and then click Calculated Item.
• In the Name box, select the calculated item.
• In the Formula box, edit the formula.
• Click Modify.