new question on FALSE

[Pat]

=IF(BL484="saleable",(SUBSTITUTE(TRIM(BS484&" "&BT484)," ",",")),"")

Earlier I was kindly given a solution where the above formula was modified
to prevent FALSE being returned.

I now have formatted the column BL to give it a custom format. This mean now
that instead of "saleable" being entered a TRUE value ie. 1 or above is
entered. This now creates its another problem, instead of BL486 being
"saleable" it is in fact 1 or TRUE.

What can be done with the above formula to get around this?

Thanking you in advance!

Pat

 

[Frank Kabel]

Hi Pat
try
=IF(BL484,(SUBSTITUTE(TRIM(BS484&" "&BT484)," ",",")),"")

HTH
Frank

 

[Frank Kabel]

Hi Pat
now I'm confused. That do you exactly enter in cell BL484? a string
(e.g. 'saleable') or a number (e.g. 1) which can be displayed as TRUE
or FALSE. Or did you enable a data validation for this cell (Menu entry
'Data - Validation')?

maybe you can explain this with a little bit more detail
Frank

 

[Pat]

Frank

Sorry but this will not identify "saleable" or "onhold" as this is what the
custom format allows for BL484.

Pat

 

[Pat]

Hi Frank

Cell BL484 has a custom format "saleable";"saleable";"onhold"

If I want "saleable" to display I enter 1
If I want "onhold" to display I enter 0

I could use Data/Validation/Allow: List/Source: saleable,onhold

By using the first option ie. custom format, this allows for faster data
entry.

Perhaps its not possible for a formula to read a cell that has a custom
format, as described!

What do you think?

Pat

 

[Frank Kabel]

Hi Pat
now it becomes clear - never used a custom format this way but nice
idea
You can then just simple check for 1 or 0 (the display is not relevant,
just the true value behind this format). So try
=IF(BL484=1,(SUBSTITUTE(TRIM(BS484&" "&BT484)," ",",")),"") -> for
saleable
this can be 'simplified to
=IF(BL484,(SUBSTITUTE(TRIM(BS484&" "&BT484)," ",",")),"")

if you want to check that either 0 or 1 is entered try the following
==IF(BL484,(SUBSTITUTE(TRIM(BS484&" "&BT484),"
",",")),IF(NOT(BL484),"onhold entered",""))

Frank

 

[Pat]

Frank, you are a genius

I thought it couldn't be done!

Cheers
Pat

 

[Frank Kabel]

Hi Pat
thanks for the thanks
Frank