Forum Discussion

Faisal_UPM's avatar
Faisal_UPM
Copper Contributor
Jan 11, 2024

Linked Excel Sheet #VALUE Error

Good morning

 

I have linked data from an array from one Excel workbook to another (i.e. separate files). When the data from an array in one workbook is pulled to the other, the destination workbook has formulae which are applied to the imported data, however, they return a #VALUE error. But if the data is pasted as values manually, the formulae work and return correct values.

 

The idea is, when I drop the Excel files in the same folder, one will pull the values from the other and use the imported data to conduct internal calculations and update charts.

 

p.s. I am using MS 365 at work so I am assuming it is the latest version of Excel. 

 

I would appreciate any assistance

Excel
Formulas and Functions
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Jan 12, 2024

    Faisal_UPM 

    The #VALUE! error in Excel usually indicates a problem with the data type or structure in a formula. When working with linked data between different workbooks, there are a few potential reasons for encountering this error. Here are some steps you can take to troubleshoot and resolve the issue:

    1. Check Cell References:
      • Ensure that the cell references in your formulas are correct and are pointing to the correct cells in the linked workbook.
      • Verify that the linked workbook is open when you are trying to access the linked data.
    2. Data Type Mismatch:
      • Make sure that the data types in the source and destination workbooks match. For example, if you are pulling a date from one workbook to another, ensure that both workbooks interpret it as a date.
    3. Array Formulas:
      • If you are using array formulas, ensure that they are set up correctly, and the array dimensions match.
    4. Calculation Settings:
      • Check the calculation settings in both workbooks. Make sure that automatic calculation is enabled to ensure that formulas are recalculated when changes occur.
    5. Error Checking:
      • Use the "Trace Dependents" and "Error Checking" features in Excel to identify any potential issues with your formulas. Excel will provide suggestions and explanations for formula errors.
    6. External Workbook Path:
      • Verify that the path to the linked workbook is correct and accessible. If the linked workbook is moved or deleted, it can result in #VALUE! errors.
    7. Dynamic Arrays:
      • If you are using Excel 365 with dynamic arrays, be aware that dynamic arrays might not be supported in older versions of Excel. Ensure that both workbooks are using compatible versions.
    8. Formula Recalculation:
      • Manually trigger a recalculation of formulas by pressing F9 or going to the Formulas tab and clicking on "Calculate Now."

    The text and steps were edited with the help of AI.

    If the issue persists, you may need to provide more specific details about the formulas and the structure of your workbooks for further assistance.

    In this link you will find some more information about it:

    Welcome to your Excel discussion space!

     

    My answers are voluntary and without guarantee!

     

    Hope this will help you.

    Was the answer useful? Mark as best response and like it!

    This will help all forum participants.

Resources