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
> <tanstaafl@libertytrek.org> 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
> > <tanstaafl@libertytrek.org> 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
> > > <tanstaafl@libertytrek.org> wrote:
> > > > On Mon Jan 16 2017 18:16:28 GMT-0500 (Eastern Standard Time), m.a.riosv
> > > > <miguelangelrv@libreoffice.org> 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+unsubscribe@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