Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a pivot table in Excel 2007 which I’ve pasted it into Word 2007 using Paste Special (Paste link) -> Microsoft Office Excel Worksheet Object.

The pivot table appears in Word and the link to Excel is working. The problem is that if the pivot table expands (for example, due to showing 12 months of data rather than six months) the link to the pivot table in Word will only show the range cells that were originally copied over with the pivot table.

I understand why this happens. When I paste as a link to Word the underling field codes look like this:

{LINK Excel.Sheet.8 "C:\Users\myAccount\Documents\testexcel.xlsx" "Sheet2!R1C1:R8C2" \a \p}

The codes refer to a fixed area (e.g., Sheet2!R1C1:R8C2 ) of the Excel spreadsheet, and so when the pivot table expands, the expanded cells fall outside the area that is defined in the field codes.

Is there some way to have the link refer to the pivot table itself rather than the cell range that happened to be originally copied over from Excel?

share|improve this question

2 Answers 2

Name the range, Sheet2!R1C1:R8C2, as an Excel table (Insert->Tables-Table). Let's call this tbSheet2

Create a name that points to the table(Formulas->Defined Names->define Name). Let call it sh2Table and point it to tbSheet2[#All]

Change the file code to the table name. With the example below, the change is:

{LINK Excel.Sheet.8 "C:\Users\myAccount\Documents\testexcel.xlsx" "Sheet2!**sh2Table**" \a \p}

share|improve this answer

The syntax for referring to a pivot table field is:

alt text

data_field is the value you are looking for i.e. SumofRevenue

pivot_table is the upper left cell of the pivot table

field1 is the name of the criteria to limit by i.e. Month

item1 is the limiting value i.e. Feb

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.