Glad we all could help and thanks for the feedback.


Cheers,
Shane Devenshire


"Mike Rogers" wrote:

Shane

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

Mike Rogers


"Shane Devenshire" wrote:

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)

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Mike Rogers" wrote:



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 zeros. Any ideas???

Mike Rogers