Re: [libreoffice-users] Re: Calc - blank cell not treated as 0 in formula when referenced from external file/sheet

2017-01-18 Thread Tanstaafl 
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


On Tue Jan 17 2017 16:15:55 GMT-0500 (Eastern Standard Time), Tim Lloyd
 wrote:
> 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 :)


-- 
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

References to external files - relative vs full path - WAS Re: [libreoffice-users] Re: Calc - blank cell not treated as 0 in formula when referenced from external file/sheet

2017-01-18 Thread Tanstaafl 
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?


On 1/17/2017, 5:20:53 PM, m.a.riosv  wrote:
> Set up references as relative path is on:
> 
> Menu/Tools/Options/LibreOffice/Load-Save/General
> 
> Miguel Ángel.


-- 
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Re: [libreoffice-users] Re: Calc - blank cell not treated as 0 in formula when referenced from external file/sheet

2017-01-17 Thread Tim Lloyd 
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 :)

Cheers

On 18/01/17 07:40, Tanstaafl wrote:

Guess it would help to attach the files...


On Tue Jan 17 2017 13:01:13 GMT-0500 (Eastern Standard Time), Tanstaafl
 wrote:

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.

On Tue Jan 17 2017 12:46:05 GMT-0500 (Eastern Standard Time), Tanstaafl
 wrote:

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.


On Tue Jan 17 2017 12:26:26 GMT-0500 (Eastern Standard Time), Tanstaafl
 wrote:

On Mon Jan 16 2017 18:16:28 GMT-0500 (Eastern Standard Time), m.a.riosv
 wrote:

Please could you share sample files to test?

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










--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Re: [libreoffice-users] Re: Calc - blank cell not treated as 0 in formula when referenced from external file/sheet

2017-01-17 Thread Tanstaafl 
Guess it would help to attach the files...


On Tue Jan 17 2017 13:01:13 GMT-0500 (Eastern Standard Time), Tanstaafl
 wrote:
> 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.
> 
> On Tue Jan 17 2017 12:46:05 GMT-0500 (Eastern Standard Time), Tanstaafl
>  wrote:
>> 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.
>>
>>
>> On Tue Jan 17 2017 12:26:26 GMT-0500 (Eastern Standard Time), Tanstaafl
>>  wrote:
>>> On Mon Jan 16 2017 18:16:28 GMT-0500 (Eastern Standard Time), m.a.riosv
>>>  wrote:
 Please could you share sample files to test?
>>>
>>> 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
>>>
>>
>>
> 
> 


-- 
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Re: [libreoffice-users] Re: Calc - blank cell not treated as 0 in formula when referenced from external file/sheet

2017-01-17 Thread Tanstaafl 
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.

On Tue Jan 17 2017 12:46:05 GMT-0500 (Eastern Standard Time), Tanstaafl
 wrote:
> 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.
> 
> 
> On Tue Jan 17 2017 12:26:26 GMT-0500 (Eastern Standard Time), Tanstaafl
>  wrote:
>> On Mon Jan 16 2017 18:16:28 GMT-0500 (Eastern Standard Time), m.a.riosv
>>  wrote:
>>> Please could you share sample files to test?
>>
>> 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
>>
> 
> 


-- 
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Re: [libreoffice-users] Re: Calc - blank cell not treated as 0 in formula when referenced from external file/sheet

2017-01-17 Thread Tanstaafl 
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.


On Tue Jan 17 2017 12:26:26 GMT-0500 (Eastern Standard Time), Tanstaafl
 wrote:
> On Mon Jan 16 2017 18:16:28 GMT-0500 (Eastern Standard Time), m.a.riosv
>  wrote:
>> Please could you share sample files to test?
> 
> 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
> 


-- 
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Re: [libreoffice-users] Re: Calc - blank cell not treated as 0 in formula when referenced from external file/sheet

2017-01-17 Thread Tanstaafl 
On Mon Jan 16 2017 18:16:28 GMT-0500 (Eastern Standard Time), m.a.riosv
 wrote:
> Please could you share sample files to test?

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

-- 
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted