#REF! with External Data Source

[fuzzzrite]

Hi there,

I am new to LibreOffice Calc from Google Sheets so this question may be quite basic.

I have a sheet that is Linked to External data refreshing an HTML table every minute. I have a separate sheet where I am referencing the cells from this sheet. My reference formula is like below:
=$Small_Funds_Data.A6

When the HTML table updates, it may return less or more rows. When the referenced cell is empty, I get an error like this:
=$Small_Funds_Data.A$#REF!

How can I avoid showing the #REF!? I’ve tried using IFERROR or IFNA but no luck.

Thanks in advance.

[joshua4]

Something or someone is deleting row 6 on the Small_Funds_Data sheet. That update/macro/person should instead be deleting the content of the rows it wants to purge, not the rows themselves.

The “easiest” solution, however, is to reference the source cell using INDIRECT(). Use

=INDIRECT("$Small_Funds_Data.$A$6")

on your reporting sheet instead of what you have shown. Using INDIRECT will detach the formula from an actual, specific row and instead work purely on the symbolic address.

Warning: Now you have a formula with yet another way to point to something different from what you were expecting.

–PS: If you see a #REF! in your cell edit line it will always be this kind of a break, not something that an error function can handle [other than just to have a chain to yet another cell that references the broken cell via something like =IFERROR(BrokenCellAddress,"") and hiding the offending cell or its text.] This is different from getting a #REF! as the result that shows in a cell itself.

[fuzzzrite]

Thanks. Indirect sort-of works, but I can’t drag down the formula. I guess I need to find a way to automate this or it’s gonna be a nightmare to create all the references.

[Villeroy]

How to reproduce and understand #REF! errors:

1. Get some blank sheet
2. Formula in cell F5: =E4 (this is a reference to the upper-left neighbour cell)
3. Copy F5 upwards until row 1. F1 reads =F#REF! because there is no upper neighbour in column F.
4. Copy F5 to the left until row A. A5 reads F1 reads =#REF!4 because there is no left neighbour in row 4.

[joshua4]

It seems like there would be some sort of “key” in your external data for each record that you want to look at. So, instead of doing each reference, somehow you could use INDIRECT for a range (“array” for VLOOKUP) and look up the specific record via a key.

If you go ahead an upload an example ODS (as you should usually do) the community might have a quick solution for you.