-1

I have a large table with 20 columns in Excel, out of which I just need 4 columns to display in Word Document dynamically.

However, only data in the first cell of the range (that contains 4 columns and 30 rows) gets displayed in the Word document.

I have gone through different forums for a solution, but couldn't find one. Any solution / workaround shall be highly appreciated. Thanks.

I first went to the Excel, created a Named Range, named as 'InitTechStatus' with the below formula.

=INDEX(Tbl_AllDetails[#All],SEQUENCE(ROWS(Tbl_AllDetails[#All])),{1,2,4,8})

I am able to use the range 'InitTechStatus' elsewhere in Excel. However, I am unable to link to this range in Word Document.

I used the following in Word Document to link to the Excel

{ LINK Excel.Sheet.12 "E:\ProjData\ProjStatus.xlsx" "Sheet2!InitTechStatus" \a \f 5 \h * MERGEFORMAT }

R K
  • 1
  • 2

1 Answers1

0

I used a workaround to achieve my objective. I went to an unused part of the worksheet, say, Cell 'AC23'. I typed the formula I require in the cell AC23. In my case, it is =INDEX(Tbl_AllDetails[#All],SEQUENCE(ROWS(Tbl_AllDetails[#All])),{1,2,4,8}). The data spills into the adjacent rows and columns.

Then I went to the Named Ranges and under the name, InitTechStatus I gave the formula =Sheet1!$AC$23#. The # denotes to excel that the spilled data from that cell is also included.

I later on used the following in Word Document to link to the specific Excel data that I require.

{ LINK Excel.Sheet.12 "E:\ProjData\ProjStatus.xlsx" "Sheet2!InitTechStatus" \a \f 5 \h * MERGEFORMAT }

The switches in the Link in the Word Document can also be changed as required. Excel to Word: Manipulating Field Codes and switches

Tyler2P
  • 2,324
  • 26
  • 22
  • 31
R K
  • 1
  • 2