new question on FALSE

  • Thread starter Thread starter Pat
  • Start date Start date
P

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
 
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
 
Frank

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

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
 
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
 
Back
Top