#value!

W

wal50

I am using the following which puts a 0 in the field when the lookup finds no
match.
=IF(ISERROR(VLOOKUP($D191,'0604'!$A$1:$E$141,5,FALSE)),0,VLOOKUP($D191,'0604'!$A$1:$E$141,5,FALSE))
This works fine except I don't want a 0, I want a blank. But when I use " "
instead of 0, the cell shows as blank but when I sum that column I get #Value!
Any ideas?
Thanks
WAL50
 
P

pdberger

Wal50 --

Try not using " ", but "". The first puts a space in the cell (you just
can't see it), but the second actually leaves it blank.

HTH
 
D

David Biddulph

wal50 said:
I am using the following which puts a 0 in the field when the lookup finds
no
match.
=IF(ISERROR(VLOOKUP($D191,'0604'!$A$1:$E$141,5,FALSE)),0,VLOOKUP($D191,'0604'!$A$1:$E$141,5,FALSE))
This works fine except I don't want a 0, I want a blank. But when I use "
"
instead of 0, the cell shows as blank but when I sum that column I get
#Value!
Any ideas?
Click to expand...

Are you actually summing the column (with the SUM fumction), or are you just
adding the values?
I find that SUM() can cope with blanks, but addition doesn't.
 
D

Domenic

Try...

=IF(ISNA(VLOOKUP($D191,'0604'!$A$1:$E$141,5,FALSE)),"",VLOOKUP($D191,'060
4'!$A$1:$E$141,5,FALSE))

Then use the SUM function to sum. It ignores text values, including
formula blanks. So, for example, you could try something like this...

=SUM(A2:A10)

or

=SUM(A2,A5,A10)

Hope this helps!
 
W

wal50

Thanks, but the result is the same. Still get #value! when I sum. The sum
works when I replace with 0.
WAL50
 
W

wal50

That was it. I was adding (alternate columns); summing solves the problem.
Thank you all for the suggestions.
WAL50
 
Top