Calc - blank cell not treated as 0 in formula when referenced from external file/sheet

users
#1

Hello,

Before I go open a bug for this, would appreciate if someone could confirm.

Using 5.2.3 on Windows 7 x64...

I have a spreadsheet - call it 'A' - with 9 sheets in it, and lots of
formulas/references between the sheets. These all work fine and have for
a very long time.

I am working on a new spreadsheet - call it 'B' - that will have a very
similar master sheet that will pull from spreadsheet 'A'.

Since the only sheet in this new spreadsheet will be very similar to the
main sheet on spreadsheet 'A', I started with a copy of 'A', then
deleted all of the sheets I wouldn't need except the one I will be
pulling data from. the plan was to get the formulas (math formulas)
working with sheet references first, then once they were working, change
the sheet reference to the external reference.

Everything went as planned, with one exception.

Some of the cells are blank on the sheet being pulled from. The formulas
correctly interpreted the blank cells as zeros while I was using sheet
references.

But when I changed these to reference the exact same cell, but in the
other spreadsheet 'A', I got the '#VALUE!' error.

Is this an expected difference when referencing an external file/sheet?

#2

Anyone?

This one is going to be a real problem if I can't solve it.

Thanks

#3

Please could you share sample files to test?

Miguel Ángel

#4

I can't share the specific files, they contain sensitive financial
information.

But it should be easy to recreate...

Create two spreadsheets, 'A' and 'B'

Define column A in spreadsheet 'A' as Number/Currency, enter some
numbers in column A

Define column A in spreadsheet 'B' as Number/Currency, define formula to
reference column A in spreadsheet 'A', fill down to include rows in
spreadsheet 'A' with nothing in them

Sort column A in spreadsheet 'B' in descending order

Note that all of the blank cells are sorted ABOVE the ones with values,
when they should be sorted BELOW (treated as zeros, so less than any non
blank/zero value).

Sorting the same rows directly in spreadsheet 'A' puts the empty cells
BELOW the cells with values

#5

Scratch this... I actually decided to go ahead and create these for
anyone who asked, but discovered these work as expected.

So, it must have something to do with the VLOOKUP I'm using. I'm working
on recreating these, and will follow-up once I'm done.

That said, if anyone knows how to use relative references to external
spreadsheets (meaning, the reference assumes the referenced spreadsheet
is in the same location/directory as the one calling it), I'd be most
appreciative.

#6

Ok, I created two basic spreadsheets that demonstrate the problem, but
you'll have to adjust the formula for the file location on your computer
- unless someone can tell me how to make it just a relative reference
that assumes the called document is in the same location as the one
calling it.

#7

Guess it would help to attach the files...

#8

Hi, the list doesn't accept attachments. Best bet is either the web based interface:

http://www.libreoffice.org/get-help/nabble/

Or use an online storage resource we can look at :slight_smile:

Cheers

#9

Set up references as relative path is on:

Menu/Tools/Options/LibreOffice/Load-Save/General

Miguel Ángel.

#10

Thanks Miguel, but...

I was thinking more of how to do this in the formula itself.

Using this option - which apparently is enabled by default since mine
was, and upon testing, I discovered the reference is indeed relative.

But, this is confusing - when looking at the formula, it shows the full
path to the file.

So, it appears there is no way to use full paths in one document, and
relative paths in a different document?

#11

I decided this was most probably a bug, so I went ahead and opened one
and attached the files to it, so you can download them from the bug here:

https://bugs.documentfoundation.org/show_bug.cgi?id=105414

#12

... unless someone can tell me how to make it just a relative reference that assumes the called document is in the same location as the one calling it.

Set up references as relative path is on: Menu/Tools/Options/LibreOffice/Load-Save/General

Thanks Miguel, but... I was thinking more of how to do this in the formula itself. Using this option - which apparently is enabled by default since mine was, and upon testing, I discovered the reference is indeed relative. But, this is confusing - when looking at the formula, it shows the full path to the file.

It is indeed confusing - but it does do what you need. As I happened to mention very recently in another thread, Calc always expands relative references and displays absolute references in formulae, e.g. in the Input Line, which may give the mistaken impression that the relative addressing is not working. But both what is saved in the document file and the effect when it is reopened elsewhere are indeed controlled by the "Save URLs relative to file system" option at the location specified above.

So, it appears there is no way to use full paths in one document, and relative paths in a different document?

Surely there is? You just need to make the appropriate selection in Options before you save each document file. Note that, although many Options apply to your installation of LibreOffice or to the way documents are displayed, some - like this one - also or instead affect the saved version of the particular document you are working on.

I trust this helps.

Brian Barker