I asked a very similar question a while back and got some rally good answers:

http://groups.google.com/group/micro...1?q=gsnu2007xx
--
Gary''s Student - gsnu2007L


" wrote:

Hi all,

I was hoping for some advice - I've got Index function pulling data
and (as expected) in cases where the data-source cell is "", the Index
returns '0'. The point is that both "" and Zeros are valid values in
the data-source cells and what I'd need would be the cell, that
contains the Index would return exactly what's in the data-source
cells - if source= "", then would return "", if zero or anything else
- would return zero or anything else.
Currently I'm using the:
If(Index="","",index)
but because the 'Index' bit is actually quite a long statement, the
function is just way too long for my liking and i was hoping there was
a smarter way of achieving the same result. The Tools-options-zero or
Cell formatting doesn't work as it hides also the valid zeros...

What seem to work is UDF:

Public Function nonzero(myLookup)
If myLookup = vbNullString Then
nonzero = vbNullString
Else
nonzero = myLookup
End If
End Function

And then in the cell I'd have =nonzero(index). But, (1) i don't know
if the code i've written is correct (i.e., if "vbnullstring" is the
constant i should be using); (2) it requires users to enable macros as
otherwise the function wouldn't work.

So, all in all, i was hoping that there was a bit more elegant/
accurate solution than the above and perhaps using just .xls default
functions (i.e., avoiding UDF/xla etc.).

Any ideas appreciated!