Ask Your Question

#ref! showing in .xls file

asked 2017-03-18 03:51:27 +0200

Stephen gravatar image

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.

edit retag flag offensive close merge delete


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.

Mike Kaganski gravatar imageMike Kaganski ( 2017-03-18 05:57:30 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2017-03-18 07:22:34 +0200

Stephen gravatar image

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

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?'




edit flag offensive delete link more


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?

Mike Kaganski gravatar imageMike Kaganski ( 2017-03-18 07:30:08 +0200 )edit

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.

robleyd gravatar imagerobleyd ( 2017-03-18 07:43:39 +0200 )edit

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

Mike Kaganski gravatar imageMike Kaganski ( 2017-03-18 09:08:19 +0200 )edit
Stephen gravatar imageStephen ( 2017-03-18 10:09:31 +0200 )edit

answered 2017-03-18 11:20:18 +0200

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.

edit flag offensive delete link more


Thanks for your comments Mike.

Stephen gravatar imageStephen ( 2017-03-19 05:05:09 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-03-18 03:51:27 +0200

Seen: 37 times

Last updated: Mar 18 '17