Excel worksheet link to external worksheet

👁 Image

KAR FUNG 61 Reputation points

Hi Sir,
I have a worksheet which contains lot of section date. This worksheet will be updated from time to time.

I wish to link the data worksheet to another working worksheet. My way is to copy the entire data worksheet and paste in the new sheet of the working worksheet. However, the defined name manager wasn't pasted. Kindly advise.

Secondly, I may use the Get Data function in the tab of the data. However, the defined name manager wasn't brought over.

Third, any method to link with the external worksheet for the reference data and can use it like feel it link with other sheet. Kindly advise.

Thanks.

0 comments No comments

5 answers

AI answer
To keep the defined names and still have the data “live‑linked” to the original workbook, use workbook links instead of copying the sheet as values.

To link ranges (and keep them updating)

Use workbook links (external references) from the “working” workbook to the “data” workbook:

Open both workbooks: the source (data) workbook and the destination (working) workbook.

In the source workbook, select the cell or range to reuse.

Press Ctrl+C (or Home → Clipboard → Copy).

Switch to the destination workbook and select the cell where the data should appear.

On Home → Paste → Paste Link.

Excel creates formulas like:

='C:\Path\[DataWorkbook.xlsx]Sheet1'!A1

When the source workbook changes, the destination workbook updates.

If the source workbook is on a web/FTP location, the link can be typed directly, for example:

='http://server/path/[file.xls]Sheet1'!A1 ='ftp.server.somewhere/file.xls'!MyNamedCell

To use defined names from the external workbook

If the source workbook already has defined names, link to those names instead of copying the sheet:

Open the destination (working) workbook and the source (data) workbook.

In the destination workbook, select the cell(s) where the data should appear.

Type =.

Switch to the source workbook and the sheet that contains the named range.

Press F3, choose the defined name, and press Enter.

Excel creates a formula that references that defined name in the external workbook. If the named range covers multiple cells, Excel can spill the results as a dynamic array (in current Microsoft 365 versions) or as a legacy array formula (Ctrl+Shift+Enter) as described in the documentation.

To link to a specific place in another workbook via hyperlink

If the goal is navigation (jumping to the external data) rather than pulling values:

In the source workbook (where the link will live), select the cell or object that should act as the link.

Insert → Link (or right‑click → Link, or Ctrl+K).

Under “Link to”, choose “Existing File or Web Page”, select the external workbook, then click “Bookmark”.

Choose the sheet and cell reference or a defined name in that workbook, then OK.

This does not copy the defined names into the new workbook, but lets the workbook jump directly to the named range in the external file.

Why Name Manager entries do not copy

Copying or importing data (including via Get Data/Power Query) brings in values or query results, not the defined names themselves. Defined names are workbook metadata and must be recreated or referenced across workbooks using the methods above; they are not transferred automatically by copy/paste or Get Data.

References:

Create workbook links

Create a workbook link between cells in different workbooks

Create a workbook link to a defined name in another workbook

Work with links in Excel

Create a link to a specific location in a workbook

Create or change a cell reference
AI-generated content may be incorrect. Read our transparency notes for more information.

👁 Image

Senthil kumar 685 Reputation points

Hi @KAR FUNG

Simple way to use get data method.

Open one new excel goto Data->GetData->FromFile->From Excel Workbook.

select your excel location and import the data. once imported save the file. suppose any changes happened in source excel file. just click goto Data->refresh all in your import excel file. automatically updated to your excel file.

👁 refreshdata

Thanks.

👁 Image

KAR FUNG 61 Reputation points

I have tried this method, anyway to set the display to be similar to the file that I linked and the defined NAMES will copy along in the new file?
👁 Image

Senthil kumar 685 Reputation points

ok understand. in this case you have to copy the entire excel and paste it another location and paste this formula =[sourceexcelfilename(Book1).xlsx]Sheetname(Sheet1)!A1:A10 may work for you. better method move or copy it will replicate everything. formatting color everything but both files should open and sheet by sheet you have move.

Thanks.
👁 Image

KAR FUNG 61 Reputation points

this formula same with type "=" in the cell and select the cell on the link file right?
This way won't work with the appearance I wanted. Do you have anyway to link the external Excel file prefectly with copy all the defined NAMES?
👁 Image

Senthil kumar 685 Reputation points

suppose VBA code is ok for you. i will try and confirm to you.
👁 Image

KAR FUNG 61 Reputation points

I am ok with VBA.
👁 Image

Senthil kumar 685 Reputation points

VBA code is worked but not updated properly so i am checking any other ways.

👁 Image

Senthil kumar 685 Reputation points

Sub Button1_Click()
 Dim srcPath As String
 Dim srcWb As Workbook, dstWb As Workbook
 Dim ws As Worksheet, newWs As Worksheet
 Dim nm As Name
 Dim srcName As String, srcDir As String
 Dim usedR As Range, addr As String
 ' ===== SET YOUR SOURCE FILE PATH HERE =====
 srcPath = "D:\Test\test.xlsx"
 ' ==========================================
 Application.ScreenUpdating = False
 Application.DisplayAlerts = False
 Application.Calculation = xlCalculationManual
 Set dstWb = ThisWorkbook
 Set srcWb = Workbooks.Open(srcPath, UpdateLinks:=False)
 srcName = srcWb.Name
 srcDir = srcWb.Path
 ' ---- 1. Copy every sheet (formatting, colors, sheet-scoped names intact) ----
 For Each ws In srcWb.Worksheets
 On Error Resume Next
 dstWb.Worksheets(ws.Name).Delete
 On Error GoTo 0
 ws.Copy After:=dstWb.Worksheets(dstWb.Worksheets.Count)
 Set newWs = dstWb.Worksheets(dstWb.Worksheets.Count)
 newWs.Name = ws.Name
 Next ws
 ' ---- 2. Write ONE external-link formula per used range (fast) ----
 For Each ws In srcWb.Worksheets
 If Application.WorksheetFunction.CountA(ws.UsedRange) > 0 Then
 Set newWs = dstWb.Worksheets(ws.Name)
 Set usedR = ws.UsedRange
 addr = usedR.Address ' e.g. $A$1:$F$5000
 ' Single assignment fills the whole block with relative-adjusted
 ' external references in one shot.
 newWs.Range(addr).Formula = _
 "='" & srcDir & "\[" & srcName & "]" & ws.Name & "'!" & _
 usedR.Cells(1, 1).Address(False, False)
 End If
 Next ws
 ' ---- 3. Recreate WORKBOOK-SCOPED defined names as external links ----
 For Each nm In srcWb.Names
 If InStr(nm.Name, "!") = 0 Then ' workbook-scoped only
 On Error Resume Next
 dstWb.Names.Add Name:=nm.Name, _
 RefersTo:="='" & srcDir & "\[" & srcName & "]" & Mid(nm.RefersTo, 2)
 On Error GoTo 0
 End If
 Next nm
 ' (Sheet-scoped names already transferred with the copied sheets in step 1)
 srcWb.Close SaveChanges:=False
 Application.Calculation = xlCalculationAutomatic
 Application.ScreenUpdating = True
 Application.DisplayAlerts = True
 MsgBox "Done — fast range-based linking complete." & vbCrLf & _
 "Sheets, formatting, and names linked. Links refresh on open.", vbInformation
End Sub

try this code and give your feedback

👁 Image

Kristen-L 13,280 Reputation points • Microsoft External Staff • Moderator
Hi KAR,

Thank you for your clarification.

Based on your request, unfortunately it's not available in current Excel built-in function to use Define Name in external file into the local data validation list. In Excel, copy paste or Get Data feature only imports data/datasets, not the source workbook’s Name Manager definitions. Define Name Manager is a local workbook-level metadata entry, and Excel cannot automatically recreate/export Name Manager definitions from a workbook into another one.

Since data validation list also does not reliably allow direct external workbook references as the list source, the recommend way for your case is to manually recreate a local data source:
- Import or link the external list into a local helper sheet/table in the current/local working workbook.
- Create a local defined name over that local helper range and use that new defined names in Data Validation.
I hope this provides a helpful approach for your scenario. If you need any further assistance or clarification, please feel free to let me know. I’ll be glad to help.

If you found the answer helpful, I would truly appreciate it if you could mark it as accepted. Doing so helps highlight the solution for others who may be searching for similar information in the community. Your contribution not only supports fellow users in finding the right guidance more quickly, but also helps build a more resourceful and collaborative space for everyone.

Thank you once again, and I wish you a wonderful day.
👁 Image

Kristen-L 13,280 Reputation points • Microsoft External Staff • Moderator

Hi KAR,

I hope you’re doing well.

Just checking in on the support thread to see if my previous response helped resolve your issue. If you’re still having trouble or have any questions, feel free to reach out. I’m happy to assist anytime. Thanks again for your collaboration, and I look forward to hearing from you soon.

Warm regards.

👁 Image

KAR FUNG 61 Reputation points

Any method, that I can import the data file and names for the data file?
Sign in to comment
👁 Image

KAR FUNG 61 Reputation points

I wish to use the defined cell in the manager name from the external excel file in the LIST of the data validation. However, it's not working due to excel not allowed external reference in the data validation. Kindly advise any method to resolve this. Thanks.

0 comments No comments

Sign in to comment
👁 Image

Kristen-L 13,280 Reputation points • Microsoft External Staff • Moderator
Hi KAR,

Thank you for your question.

A defined name in Excel is typically managed at the workbook level (though it can also be limited to a specific worksheet). This means that defined names are not part of the cell contents themselves, but rather workbook-level metadata maintained via Formulas > Name Manager.

Based on this, the behavior you are experiencing is expected:
- Copying and pasting a worksheet or its contents does not automatically recreate workbook-level defined names in the destination workbook.
- Using Get Data imports the data (tables, ranges, or named ranges as datasets), but it does not transfer the original workbook’s Name Manager entries into the new workbook.
For reference: Use the Name Manager in Excel | Microsoft Support

Here are some suggestions you may consider:

Option 1: Use Excel Tables with Get Data
1. In the source workbook, convert your data range into an Excel Table (Ctrl + T).
2. Assign a meaningful table name (e.g., tbl_Reference). Tables support structured references that automatically adjust when data changes.
3. In the working workbook:
  
  Go to Data > Get Data > From File > From Excel Workbook
  
  Select your source file and load the data into a helper sheet (e.g., RefData)
4. You can then:
  
  Use the imported table directly in formulas, or
  
  Create new defined names locally that refer to the imported table or its columns
For reference: Using structured references with Excel tables | Microsoft Support

Option 2: If you require the Name Manager entries in the destination workbook

If you need to transfer the entire worksheet, use the Move or Copy sheet feature from the sheet tab (right-click the tab and select Move or Copy) instead of copying only the cells. This is the correct method for copying a full worksheet to another workbook.

After transferring, go to Formulas > Name Manager in the destination workbook.

Recreate any necessary workbook-level defined names there.

For reference: Move or copy worksheets or worksheet data | Microsoft Support

👁 User's image

I hope this helps clarify the behavior and provides a workable solution. Please feel free to reach out if you need further assistance.

Thank you for your patience and understanding.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
👁 Image

Kristen-L 13,280 Reputation points • Microsoft External Staff • Moderator

Hi KAR,

I hope you’re doing well.

Just checking in on the support thread to see if my previous response helped resolve your issue. If you’re still having trouble or have any questions, feel free to reach out. I’m happy to assist anytime. Thanks again for your collaboration, and I look forward to hearing from you soon.

Warm regards.
Sign in to comment

URL: https://learn.microsoft.com/en-us/answers/questions/5919508/excel-worksheet-link-to-external-worksheet

⇱ Excel worksheet link to external worksheet - Microsoft Q&A

Excel worksheet link to external worksheet

5 answers

Your answer