Re: [libreoffice-users] Re: Calc - blank cell not treated as 0 in formula when referenced from external file/sheet
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
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
[libreoffice-users] Re: Calc - blank cell not treated as 0 in formula when referenced from external file/sheet
Set up references as relative path is on: Menu/Tools/Options/LibreOffice/Load-Save/General Miguel Ángel. -- View this message in context: http://nabble.documentfoundation.org/Calc-blank-cell-not-treated-as-0-in-formula-when-referenced-from-external-file-sheet-tp4205236p4205612.html Sent from the Users mailing list archive at Nabble.com. -- 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
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
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
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
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
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
[libreoffice-users] Re: Calc - blank cell not treated as 0 in formula when referenced from external file/sheet
Please could you share sample files to test? Miguel Ángel -- View this message in context: http://nabble.documentfoundation.org/Calc-blank-cell-not-treated-as-0-in-formula-when-referenced-from-external-file-sheet-tp4205236p4205510.html Sent from the Users mailing list archive at Nabble.com. -- 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