Home > Misc > Link Dynamic Range Excel Reports to Word

Link Dynamic Range Excel Reports to Word

We can link any kind of Excel Reports to Word for making of report journals. However most of the times Excel pivot table reports that can expand over the selection so integration of dynamic range pivot table to word will be some tricky.
We will use simple Excel functions COUNTA and INDIRECT to make reference of Excel dynamic ranges and intern link this Excel dynamic range to word.
The original idea is for this task is from my team lead Peter Schmidt during PoC to one of my client
Step 1: Create a Pivot table report from PowerPivot model as
Excel Pivot Table
Step 2: Calculate dynamic cell range of pivot table using COUNTA function as
Dynamic cell range
Step 3: Reference the dynamic range cell to a name using Excel Formula – Name Manager. Use the INDIRECT function to refer the cell range as
Name range
Step 4: Select the range of Pivot table and paste into word using paste spatial as
Paste special option
Step 5: Select the pasted pivot table and update link then click on Alt+F9 to check the connection string including the cell range
Excel link
Step 6: Update the cell range R1C1:R5C2 using name range SalesPivotRange created from step 3
Name range
Step 7: Expand the details on Excel Pivot table and update the link on word as
Excel range updates
Word links to dynamic range

Advertisements
Categories: Misc Tags:
  1. May 12, 2013 at 6:43 am

    At last, after looking through http://bipassion.
    wordpress.com/2013/02/24/link-dynamic-excel-reports-to-word/ for a while,
    I found a site from which I was able to really get
    worthwhile insights in regard to the studies and the
    knowledge that I want. There need to be more things like this on PivotX

  2. Alex
    May 2, 2014 at 8:25 pm

    This is great if your table always starts in A1, but can anyone tell me how to use the COUNTA and INDIRECT if you are adding/removing Pivot table filters? The problem I have is that adding more fields to the pivot filter in Excel shifts the pivot table down a row so when I go back to my Word doc, the filter rows are included in the linked table. I just want to get the cell range of the actual pivot.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: