sort order

  • Thread starter Thread starter harold
  • Start date Start date
H

harold

hi guys,
i'm trying to import from several sheets with tables of names
into another sheet using:
=sheet1!D1
=sheet1!D2
-->
=sheet5!D10
etc... in the respective cells of the new sheet... this seems fine.

the only prob is the empty cells from the reference sheets are shown in the
new sheet as 0.
when i do a sort (eg: a-z) i get all the 0 entries first then the start of
the names.
ideally i would like to not show these values as 0 (rather as empty cells),
but if i could change the sort order to have numerals after alphabet that
would suffice.

regards
harold
 
Harold

Try
=if(sheet1!D1="",na(),sheet1!D1)

all the na's will sort to the end.
You could then use another "if" function to get rid of them.

Alan
 
Rather than importing every cell, you can import the non-blank cells only by
using the formula:
=if(ISBLANK(Sheet1!D1),"",+Sheet1!D1)

If cell D1 in Sheet1 is blank, it will return a blank, otherwise it will
pick up the content of D1.
When you sort the imported data, the blanks will appear last.

krgds
WIm
 
As far as the 0's you can choose the tools/options tab and remove the
checkmark in "zero values".
 
Back
Top