Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Linking cells between spreadsheets

Copper Contributor

When linking cells between two separate spreadsheets, we receive the #REF error message. We use dropbox to store/share our files, however the person creating the link usually has no #REF error and only those accessing after creation have the erro.

1 Reply

@roccovivolo 

When you encounter the #REF! error in Excel when linking cells between separate spreadsheets, it could be due to several reasons, and using Dropbox to share files might introduce additional considerations. Here are some steps you can take to troubleshoot and resolve the issue:

1. Ensure the File Paths are Correct:

  • Make sure that the file paths in the formulas are correct for all users. Dropbox may change the file path based on the user's local setup. Verify that the file paths are consistent for both the creator and other users.

2. Check File Names and Extensions:

  • Ensure that the file names and extensions are consistent and accurate. A small discrepancy in file names or extensions can lead to #REF! errors.

3. Use Relative File Paths:

  • If possible, use relative file paths instead of absolute paths in your formulas. This helps avoid issues related to different users having different absolute paths.

4. Verify Dropbox Sync:

  • Confirm that Dropbox is fully synced for all users. Sometimes, if files are not fully synced, it can lead to referencing issues.

5. Update Links Manually:

  • Open the workbook with the #REF! error.
  • Go to the "Data" tab on the ribbon.
  • Click on "Edit Links" in the "Connections" group.
  • Select the link causing the error and click "Update Values."

6. Use the 'Insert Link' Option:

  • Instead of manually typing the link, use the 'Insert Link' option.
    • Right-click on the cell where you want the link.
    • Select "Link..."
    • Navigate to the Dropbox file and select it.

7. Check for External Content Blocking:

  • In Excel options, go to the "Trust Center" > "Trust Center Settings."
  • Check the "External Content" options and ensure that they are set to enable automatic updates.

8. Use Dropbox Shared Links:

  • Share the file using Dropbox's sharing features and use the shared link in the formulas. This can help in creating more reliable links.

9. Avoid Using Special Characters:

  • Ensure that the filenames and folder names in Dropbox do not contain special characters or spaces, as these can sometimes cause issues.

10. Update Excel:

  • Ensure that all users are using an updated version of Microsoft Excel 365. Updates can sometimes resolve compatibility issues.

If the issue persists, consider providing more specific details about the formulas or file structure for further assistance. Additionally, you might want to check if there are any restrictions or limitations imposed by Dropbox on simultaneous editing or linking from different users. The text and steps were edited with the help of AI.

 

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.