Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#value!
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,FAL SE)),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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#value!
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 "wal50" wrote: 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,FAL SE)),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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#value!
"wal50" wrote in message
... 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,FAL SE)),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? 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. -- David Biddulph |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#value!
Thanks for the response but "" gave me #value! in the sum calc too.
WAL50 "pdberger" wrote: 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 "wal50" wrote: 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,FAL SE)),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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#value!
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! In article , wal50 wrote: 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,FAL SE)),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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#value!
Thanks, but the result is the same. Still get #value! when I sum. The sum
works when I replace with 0. WAL50 "Domenic" wrote: 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! In article , wal50 wrote: 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,FAL SE)),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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#value!
Does your column contain a #VALUE! error value?
In article , wal50 wrote: Thanks, but the result is the same. Still get #value! when I sum. The sum works when I replace with 0. WAL50 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#value!
That was it. I was adding (alternate columns); summing solves the problem.
Thank you all for the suggestions. WAL50 "David Biddulph" wrote: "wal50" wrote in message ... 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,FAL SE)),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? 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. -- David Biddulph |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|