Formula showing zero's

[Mike Rogers]

I have a formula in O5 ,=CONCATENATE(K4,P10,C19,"#",C20). This formula
returns 0#0. K4 is empty until a macro inputs a number, P10 is empty until
the user inputs data, C19 has the formula
=VLOOKUP(G18,MultiCustomer,3,FALSE), and C20 has the formula
=VLOOKUP(G18,MultiCustomer,4,FALSE). How do I change the formula in O5 to
return nothing if the two lookup formulas (C19 & C20) have not looked up the
data it is designed to. If these to cells are not showing anything they are
not still not empty because they have a formula, thus causing the concatenate
formula to return the zero’s. Any ideas???

Mike Rogers

 

[Sheeloo]

You can replace C19 with IF(C19="","",C19) and similary for C20.

Or you can use ISNA in the cells having VLOOKUP like this
=IF(ISNA(Vlookup(...),"",Vlookup(...)))

If you want to supress # then you can combine it with the IF formula with
either C19 or C20....

 

[Shane Devenshire]

If your two VLOOKUP functions haven't a value to look up then they would
return #N/A errors, so it suggests that they are returning somethng and your
concatenated formula suggest that something is 0 even if the VLOOKUP doesn't
display it, possibly because those cells are formatted to not display 0.

You need to determine what value the VLOOKUP function are returning and then
you need to have the CONCATENATE function test for those. And I think I
would use & instead of concatenate.

=CONCATENATE(K4,P10,C19,"#",C20)

would become something like

=K4&P10&IF(C19=0,"",C19)&"#"&IF(C20=0,"",C20)

 

[Gary''s Student]

Go the simple route:

=IF(CONCATENATE(K4,P10,C19,"#",C20)="0#0","",CONCATENATE(K4,P10,C19,"#",C20))

 

[Mike Rogers]

Sheeloo

Tried both of your solutions and am getting the same "0#0" as a result of my
concatenate formula. If I could only get rid of the "0" that C20 is causing
I would be able to live with it. My formula in C20 is now
IF(ISNA(VLOOKUP(G18,MultiCustomer,4,FALSE)),"",VLOOKUP(G18,MultiCustomer,4,FALSE)) Any other ideas?

Mike Rogers

 

[Shane Devenshire]

It would be interesting to understand the cause of this problem - so while
you are seeing 0#0 in your formula move the C19 and select the whole formula
on the Formula Bar and press F9 - what do you see on the formula bar exactly?

Oh, and another idea, what is the format of the concatenated cell?

 

[Ken Johnson]

Sheeloo

Tried both of your solutions and am getting the same "0#0" as a result ofmy
concatenate formula.  If I could only get rid of the "0" that C20 is causing
I would be able to live with it.  My formula in C20 is now
IF(ISNA(VLOOKUP(G18,MultiCustomer,4,FALSE)),"",VLOOKUP(G18,MultiCustomer,4,FALSE))  Any other ideas?

Mike Rogers

Sounds like the VLOOKUP is returning 0 because the table has a blank
in the cell whose value is being returned.
If this is the case then maybe...

=IF(ISBLANK(VLOOKUP(G18,MultiCustomer,3,FALSE)),"",VLOOKUP
(G18,MultiCustomer,3,FALSE)) in C19 and...

=IF(ISBLANK(VLOOKUP(G18,MultiCustomer,4,FALSE)),"",VLOOKUP
(G18,MultiCustomer,4,FALSE)) in C20.

Ken Johnson

 

[Mike Rogers]

Sheeloo

After reading what everyone else suggested I found the problem. The cell
had a "0" in it ans was supressed by tools>options>view>unchecked "Zero
Values". So I changed your first suggestion to 0 instead of "" and it works.
Cudos to all..Thanks

Mike Rogers

 

[Mike Rogers]

Shane

With every ones help I figured out the problem. Thanks for the mental push...

Mike Rogers

 

[Mike Rogers]

Ken

Got it figured out that for the help!!!

Sounds like the VLOOKUP is returning 0 because the table has a blank
in the cell whose value is being returned.
If this is the case then maybe...

=IF(ISBLANK(VLOOKUP(G18,MultiCustomer,3,FALSE)),"",VLOOKUP
(G18,MultiCustomer,3,FALSE)) in C19 and...

=IF(ISBLANK(VLOOKUP(G18,MultiCustomer,4,FALSE)),"",VLOOKUP
(G18,MultiCustomer,4,FALSE)) in C20.

Ken Johnson

 

[Mike Rogers]

Shane

After reading what everyone else suggested I found the problem. The cell
had a "0" in it and was suppressed by tools>options>view>unchecked "Zero
Values". So I changed Sheloo's first suggestion to 0 instead of "" and it
works.
Cudos to all..Thanks

Mike Rogers

 

[Mike Rogers]

Gary''s Student

Thanks for the help, With every one's comment of help it all came together
and of course it was a big "DUH" and all is working fine.

Mike Rogers

 

[Shane Devenshire]

Glad we all could help and thanks for the feedback.


Cheers,
Shane Devenshire