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!

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!

Thanks Gary for your response.
It seems that i've got a bit different issue - i.e., my 'index' knows
which row/column to look for (so, it doesn't need to look for
<blanks).
e.g.:
=INDEX(A1;1;1)

The issue I've got is that the data source cell (A1) can hold string,
number, zero-length string and anything in between. All i want is the
index to return exactly what's in the A1 BUT index 'converts' zero-
length string to '0' (zero) -
i.e., if A1=0 then my function returns '0';
if A1="" then my function also returns '0'.
I realize that it's expected .xls behavior but that's not what i need
as i need the formula to return <blank if A1=<blank and return '0',
if A1='0'.
Therefor I'm using the double validation (if(index="";"";index)) but i
hoped to simplify it in some way (so that i could drop the 'if' bit).

Any other ideas?