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
<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