If formula referencing a validated cell

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have created an if formula that asks =IF('Phone-Email Contact Log'!$G2="Santa Fe",'Phone-Email Contact Log'!$J2,0)...The logical part of this equation ('Phone-Email Contact Log'!$G2="Santa Fe") is a cell that is validated. When I take off the validation, the formula produces a true- but when the validation is on it produces a false. I need the cell to be validated because the user needs to choose from a list...HELP!
 
tntangel said:
I have created an if formula that asks =IF('Phone-Email Contact
Click to expand...
Log'!$G2="Santa Fe",'Phone-Email Contact Log'!$J2,0)...The logical part of
this equation ('Phone-Email Contact Log'!$G2="Santa Fe") is a cell that is
validated. When I take off the validation, the formula produces a true- but
when the validation is on it produces a false. I need the cell to be
validated because the user needs to choose from a list...HELP!

Validation can only affect what can be entered into the cell. It cannot have
any direct effect on a formula referencing that cell. By 'direct effect' I
mean that simply turning validation on or off will have no effect.
Obviously, with validation off you could be entering different data (maybe
with an extra space character), and that will indirectly affect the formula.
 
Well that's what is weird because when I turn the validation feature off...it works like it's supposed to.
 
I have created an if formula that asks
=IF('Phone-Email Contact Log'!$G2="Santa Fe",'Phone-Email Contact Log'!$J2,0)
The logical part of this equation ('Phone-Email Contact Log'!$G2="Santa Fe")
is a cell that is validated. When I take off the validation, the formula
produces a true- but when the validation is on it produces a false. . . .
Click to expand...

How are you turning validation on and off? Just using Data > Validation
interactively? How are you using validation in this cell - as a drop-down list?
Are you changing the value in the cell manually when validation is off? Is the e
in Fe accented in the cell in question?

If you're using a validation drop-down list, then I'd suspect that there were
trailing spaces in the Santa Fe entry, which would render the result of
selecting it FALSE when compared to "Santa Fe". Further, I'd suspect that when
validation is off, you've tried entering Santa Fe manually, so without trailing
spaces, thus matching "Santa Fe", so getting the TRUE result.

In any event, try changing your formula to

=IF(TRIM('Phone-Email Contact Log'!$G2)="Santa Fe",
'Phone-Email Contact Log'!$J2,0)

and if that doesn't work, what does =LEN('Phone-Email Contact Log'!$G2) return?
 
Thank you so much for the info...it finally worked with the TRIM function...there was an extra space after the city name....you saved me!
 
Thank you so much for the info...it finally worked with the TRIM
function...there was an extra space after the city name....you saved me!
Click to expand...

[Not directed at OP.]

Yup, and I didn't even have to see the workbook. Imagine that!
 
Back
Top