How to Fix VLOOKUP #REF! Error After Copying an Excel File
Question details
The user needs to resolve a #REF! error in a VLOOKUP formula that appears after copying, sharing, or downloading a macro-enabled Excel workbook.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- A macro-enabled workbook was copied, downloaded, or shared, leading to broken data connections or lost worksheet references.
- Observed behavior
- A previously valid VLOOKUP formula suddenly returns a #REF! error because its original worksheet reference has been lost or disconnected.
Before attempting to repair the formula, verify that all referenced worksheets actually exist in the current workbook and ensure that the downloaded file is not stuck in a restricted Protected View.
Restore the Missing Worksheet Reference
Manually update the broken VLOOKUP formula to point to the correct, existing worksheet range. This is the most direct way to fix a lost reference.
When an Excel file is copied or sheets are moved incorrectly, formulas can lose track of their target data ranges. The #REF! error specifically indicates that a cell reference is no longer valid.
Click on the cell displaying the #REF! error to view its contents in the formula bar.
Look for the '#REF!' text within the VLOOKUP formula (e.g., =VLOOKUP($B$4,#REF!,2,FALSE)).
Replace the '#REF!' portion with the correct worksheet name and cell range, such as 'Equipment List'!A7:D10.
Press the Enter key to apply the corrected formula and verify that the correct value is now displayed.

Enable Macros and Update External Links
Downloaded macro-enabled workbooks often block active content and external links by default, which can cause dependent formulas to fail.
Adjust File Sharing and Access Permissions
If the copied file relies on data hosted on a shared network or cloud drive, restricted access permissions can prevent data from loading.
Easily Fix Formula Errors with WPS Office
WPS Spreadsheet provides intuitive formula auditing tools and seamless compatibility with Microsoft Excel files, ensuring your VLOOKUP formulas and macro-enabled workbooks run flawlessly.
- 1. Open the copied workbook: Launch WPS Spreadsheet and open your downloaded or copied workbook.
- 2. Locate the broken formula: Use the Error Checking feature under the Formulas tab to quickly jump to any cells displaying the #REF! error.
- 3. Correct the reference: Click into the formula bar and re-highlight the correct source data range to instantly repair the VLOOKUP formula.

Frequently Asked Questions
Why does my VLOOKUP formula turn into #REF! after copying?
This happens when the formula relies on a specific worksheet or data range that was deleted, not included in the copy process, or blocked by security settings in the new file location.
Can file sharing permissions directly cause a #REF! error?
While missing sharing permissions alone will not alter a formula's text to #REF!, restricted access to linked external workbooks can prevent data from loading, causing dependent formulas to break.
How can I prevent the #REF! error when sharing Excel files?
Before sharing your workbook, ensure all referenced data is contained within the same file. Alternatively, you can convert your VLOOKUP formulas to static text by copying the cells and using 'Paste as Values'.




