#REF error when new data is copied into a sheet

Question

Here is the formula that works for several people on different computers and all versions are 2503+

but when people open on versions 2408 and other 2400 versions, all formulas change to #REF.

Version 2500+ (document created in this version)

=(SUMIFS('SAI-41036-Q1'!$J:$J,'SAI-41036-Q1'!$D:$D,'Data Entry'!$D$70,'SAI-41036-Q1'!$F:$F,'Data Entry'!$F$70,'SAI-41036-Q1'!$K:$K,'Data Entry'!$K$70,'SAI-41036-Q1'!$M:$M,{"*\BLD"}))*2

What is causing this?

Versions 2400+ shows the #REF for all formulas

=(SUMIFS('SAI-41036-Q1'!#REF!,'SAI-41036-Q1'!#REF!,'Data Entry'!$D$70,'SAI-41036-Q1'!#REF!,'Data Entry'!$F$70,'SAI-41036-Q1'!#REF!,'Data Entry'!$K$70,'SAI-41036-Q1'!#REF!,{"*\BLD"}))*2

Background:

SAI-41036-Q1 has the quarterly data stored in it. To update this data, user is to delete the current data and paste new data back into the SAME sheet. Just using basic copy and paste to put new data into the named worksheet for quarterly data.

It appears to be the reference to the column where I don't know the end of the data so I am using just the column reference in this manner. Is this not a supported feature in version 2400+?

This also happens on another sheet reference where there is no column reference with just the column with colons (ie J:J) . I am puzzled what is causing just some users to not be able to copy and paste new data into a sheet where the name HAS NOT changed. Old data deleted, new data copied in. Formulas are also locked so the user cannot accidentally delete anything. Sheets with copied data are NOT protected. However, Sheets with the formulas are Protected with a password.

Is this a Trust Center setting? Help on this error has pointed me to those options but doesn't detail what to change.

Thanks for your help.

Answer 1

I'm narrowing the problem down to being just the version 2408 they are using. I've tested in various 25xx, 2007, 2019 and they all work. The client wants to back down the app to 24xx which I do not believe is the best option. I'm hoping their IT will update them to 2505. If there is still a problem then, I'll have to check trust center options.

If a file comes from the internet but is saved to their storage, then they open the file, is it still considered an internet source?

Answer 2

Any update?

Jose R.

Microsoft Community Support.

Answer 3

Hello again.

Thanks for the follow-up — your questions are very insightful, and I’ve looked into each of them to give you a clear path forward. Here's what I found:

Why Are Other Sheets Breaking Even Without Data Updates?

Even if a sheet wasn’t directly updated, Excel can still throw #REF! errors if:

• The workbook was saved in a newer version (e.g., 2503+) and opened in an older one (e.g., 2408).
• The formulas reference entire columns (like $J:$J), which are more prone to compatibility issues in older builds.
• The workbook is protected, and Excel can't resolve references during recalculation.

So yes — even untouched sheets can break if the formula structure or workbook version isn't fully supported in the older Excel build.

---

How to Compare Excel Versions (2400 vs. 2500+)

Microsoft introduced Compatibility Versions to manage function behavior across Excel builds. These versions affect how functions like LEN, MID, SEARCH, and even SUMIFS behave .

You can check or change the compatibility version by:

• Going to Formulas > Calculation Options > Compatibility Version (if available).
• Saving the file in .xlsx format from the newer version and opening it in the older one to see what breaks.
• Using File > Info > Check for Issues > Check Compatibility to get a list of features not supported in older versions.

---

Trust Center Settings That Might Affect Formula Behavior

Some Trust Center settings can block content or delay formula evaluation, especially if the file is:

• Downloaded from the internet.
• Opened from a network location.
• Contains external links.

To adjust:

• Go to File > Options > Trust Center > Trust Center Settings.
• Under Protected View, try disabling:
  • “Enable Protected View for files originating from the internet”
  • “Enable Protected View for files located in potentially unsafe locations”.

Also check External Content and Macro Settings if your workbook uses links or automation.

---

Known Issues with Sheet Protection

Protecting a sheet can interfere with formula recalculation if:

• The sheet is protected while data is being pasted or recalculated.
• The formula cells are locked, and Excel can't resolve references during a paste.

To avoid issues:

• Unlock all cells first, then only lock the formula cells.
• Use Go To Special > Formulas to select and lock only those cells.
• Protect the sheet afterward with the appropriate options (e.g., allow sorting, inserting rows, etc.).

---

Why Does Excel Flag =CONCAT() When Saving to Older Versions?

The =CONCAT() function is not supported in Excel 2016 and earlier. If you're saving to .xls or using compatibility mode, Excel will warn you that this function will break.

To fix:

• Use =TEXTJOIN() (if supported) or =A1&B1 style concatenation for broader compatibility.
• Or save the file in .xlsx format and avoid compatibility mode.

Jose R.

Microsoft Community Support.

Answer 4

Hi Jose,

Thank you for your response.

I was thinking they same as you said in #1 but further investigation shows other sheets with #ref that are just pointing to other sheets that weren't updated with new data copied in.

#2 how would I find the differences between versions?

#3 the cell help refers to trust center but I don't know what I'm looking for as the options.

#4 I need the protection so unskilled users don't wipe out tge formulas. Is there a reference on problems with protection.

Oddly, if it try to save to an older version of Excel but it lists cells that would be compromised like a cell with =concat which should be fine, right?

Answer 5

Hi.

Welcome to the Microsoft Community.

It seems like the issue you are experiencing with the #REF error when copying new data into a sheet might be related to the differences in Excel versions. The #REF error occurs when a reference is not valid. In your formula, using entire column references like $J:$J, $D:$D, etc., can sometimes cause compatibility issues between different Excel versions.

To resolve this issue and ensure compatibility across different Excel versions, you can try the following:

1. Instead of using entire column references, try specifying a specific range of cells that contain your data. For example, instead of using 'SAI-41036-Q1'!$J:$J, you can use 'SAI-41036-Q1'!$J$1:$J$1000 (assuming your data is within the first 1000 rows).
2. Check if the Excel versions where the issue occurs have any limitations or differences in handling entire column references. Upgrading to a newer version of Excel may also help resolve compatibility issues.
3. Ensure that the Trust Center settings in Excel are not blocking the references or causing any restrictions. You can review the Trust Center settings by going to File > Options > Trust Center > Trust Center Settings.
4. Consider protecting the sheets with formulas using a password only when necessary, as this additional layer of protection may sometimes cause unexpected issues.

By making these adjustments and considering the differences in Excel versions, you should be able to copy and paste new data into the sheet without encountering the #REF error. If the issue persists, feel free to provide more details, and I'll be happy to assist further.

Jose R.

Microsoft Community Support.