hlookup with a blank cell formula??

[dollyann]

Hi

I have created a quick dashboard with a drop down list which brings the values back by a hlookup formula

Each drop down has a different number of lines which brings the value of a colour by a piece of Jewellery - see below

Necklace -(drop down)
Blue
Green
Silver
0
0

If the values are 0 (as there are no values) what formula works with a hlookup to make the cell blank instead of 0??

If someone could answer this i would be very grateful :0)

 

[Jonmo1]

If the expected return value of the hlookup is TEXT, then use the T function...

=T(HLOOKUP(...))


Hope that helps.

 

[dollyann]

Hi

Thanks for your quick response but unfortunately the values have numbers after the colour ie, Blue056

Any other ideas?

Many thanks

 

[Jonmo1]

Hi

Thanks for your quick response but unfortunately the values have numbers after the colour ie, Blue056

Any other ideas?

Many thanks

Blue056 is still text..
It's only a number if ALL characters are numeric.

 

[dollyann]

Hi

Thank you, it worked :0)

You maybe able to help me again? On the value which come back i have a SUMPRODUCT formula which calculates each value for that item each month. So now i have blank cells instead of 0 for the description, can i do the same on the SUMPRODUCT formula for the value??

Thanks again :0) :0)

 

[Jonmo1]

Not quite sure I understand the question...

But The sumproduct should ignore blanks, or treat them as 0 (which it was before anyway)...


If it is causing problems, post the sumproduct formula in question, and the formula the sumproduct is referring to.