#ref! showing in .xls file

Editing an .xls file. Numerous cells do not dsiplay normally showing ‘#ref!’ and code such as ‘=INDEX(OFFSET($Input.A:A,shift),MATCH(F22,$Input.A:A,0)+3,)’ .

I am not conversant in formulas. I was only seeking to add some text to this file. It displays normally in Excel Online

I would appreciate any comments.
Thanks.

Looks like over-engineered, but working, formula. I couldn’t get it to return #REF! (but could get #VALUE!, #NAME?, Err:502, Err:501, Err:522), so it’s probably best if you share the (part of) problematic file to get useful answer. Also, you should specify which version of LO you use.

Thanks for your answer Mike. I can’t put the whole file as it contains personal information. But here are the formulas that generate the error messages. LO 5.2.5.1

The following show correctly in Excel Online, but in LO they generate #ref!. By the way, when opening the file, a dialogue box says ‘This file contains links to other files. Should they be updated?’

=INDEX(OFFSET($Input.A:A,shift),MATCH(F22,$Input.A:A,0)+3,)

=INDEX(OFFSET($Input.A:A,shift),MATCH(F22,$Input.A:A,0)+4,)

=INDEX(OFFSET($Input.A:A,shift),MATCH(F26,$Input.A:A,0)+3,)

That’s not enough. The question is, what is in referenced cells?

e.g., for your first formula, what is the content of shift (I assume it’s a named range), F22 (on the same sheet where the formula is), $Input.A:A (at least, which row on column A has the same value as F22). Which is the cell where the formula resides?

Post a sanitised version (without confidential info) that demonstrates the errors somewhere like dropbox and post the link here. People will prefer a site that doesn’t require registration to download your file.

I may suppose that either F22 and F26, or shift content is #REF! already.

Thank you guys. Noted

The problem is on the Input sheet. The cells C43, C50, C57, C64 contain strings which represent eferences to another sheet’s cells (CC!A1, CC!A10, CC!A6, CC!A6). The syntax of the strings is Excel’s.

(If my employee would create such a worksheet, I’d make them to redo it from scratch.)

LO uses another syntax by default: like CC.A1 (notice dot instead of exclamation). You may change it on Tools>Options>LibreOffice Calc>Formula>Custom>Details.... Be prepared for other problems though.

Thanks for your comments Mike.