pasting abs reference changes to #REF

[AuntErnie]

Hi...new to posting (but not using this forum). I have searched and tested and cannot find an answer...
I have a complex IF formula in a spreadsheet. It has abs references to a >72K row reference table =IFNA(IF(A11="","",IF(LEN(A11)=3,VLOOKUP(A11&".",'ICD10 Codes and Descriptions'!$A$2:$H$72345,5,FALSE),IF(RIGHT(LEFT(A11,4),1)<>".",VLOOKUP(A11,'ICD10 Codes and Descriptions'!$C$2:$H$72345,3,FALSE),VLOOKUP(A11,'ICD10 Codes and Descriptions'!$A$2:$H$72345,5,FALSE)))),VLOOKUP(LEFT(A11,3)&"*",'ICD10 Codes and Descriptions'!$A$2:$H$72345,7,0)&"*SubCat").
We take external data, add a column to the right of a column, then copy paste the formula. It works most of the time. On some spreadsheets, the formula changes all absolutes to #REF.
I am perplexed. Checked formatting of cells, tried opening the master file in the same and different excel.
I am hoping it is something so simple that I am missing it...help please!
Thanks!

 

[jasonb75]

Sounds like there is no sheet with the name, 'ICD10 Codes and Descriptions' in the workbook that you're pasting the formula into.

There is no workbook reference / filepath in the formula so it will only look for the the sheet in the workbook that you're pasting the formula into, not in the one that it was copied from.

 

[AuntErnie]

Thanks Jason. The formula I pasted reference the master sheet with absolutes. Thanks...
A friend of mine finally came through with a potential answer that did not work, but lead me to a solution!!! The files with an issue after pasting the formula were old .xls files. Even if you saved them in the newer .xlsx version, the paste still failed. The file has to be converted first, then it will allow over 70K rows in a referenced file. I knew it had to be something simple.