Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

excel If Statement is false but value is true

Posted on 2014-10-06
14
Medium Priority
?
242 Views
Last Modified: 2014-10-06
Dear experts,

in the formula below i'm getting "false" but in worksheet IMPORT!Q2 the value is web-only-user
Whats the problem?

=IF(IMPORT!Q2="web-only-user";"Set-CASMailbox -Identity "&A2&" -PopEnabled $false -ImapEnabled $false -ActiveSyncEnabled $false -MAPIBlockOutlookRpcHttp $true -MAPIEnabled $true -OwaMailboxPolicy $null"

Open in new window

0
Comment
Question by:Mandy_
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 4
14 Comments
 
LVL 12

Assisted Solution

by:James Elliott
James Elliott earned 800 total points
ID: 40363555
Try:

=IF(TRIM(IMPORT!Q2)="web-only-user";"Set-CASMailbox -Identity "&A2&" -PopEnabled $false -ImapEnabled $false -ActiveSyncEnabled $false -MAPIBlockOutlookRpcHttp $true -MAPIEnabled $true -OwaMailboxPolicy $null"
0
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 1200 total points
ID: 40363567
I'll add to that (and say that you need a close-bracket at the end :-) ).

=IF(lower(TRIM(IMPORT!Q2))="web-only-user";"Set-CASMailbox -Identity "&A2&" -PopEnabled $false -ImapEnabled $false -ActiveSyncEnabled $false -MAPIBlockOutlookRpcHttp $true -MAPIEnabled $true -OwaMailboxPolicy $null","")

Open in new window


If that doesn't work, try going to Formulas - Evaluate Formula so you can step into the formula while it executes.
0
 
LVL 2

Author Comment

by:Mandy_
ID: 40363692
Evaluate Formula shows me that the part  LOWER(TRIM(IMPORT!Q2))="web-only-user"  = false

but the value in  IMPORT!Q2    excactly is web-only-user


 $null","") not working


 $null""")  shows false

and
$null";"")   shows empty cell

???
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40363716
What does this give?

="+" & import!q2 & "+"

Open in new window


and

=len(import!q2)

Open in new window

0
 
LVL 12

Expert Comment

by:James Elliott
ID: 40363720
There are actually two different ascii characters for a symbol that looks like '-'. I can't remember the actual codes off hand, but try re-typing Q2 and then type the formula with exactly the same key choice.
0
 
LVL 2

Author Comment

by:Mandy_
ID: 40363752
"+web-only-user+"

and
=len(import!q2)

empty cell
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40363757
Those two answers are so strange - can you please post your spreadsheet.
0
 
LVL 2

Author Comment

by:Mandy_
ID: 40363761
if i change web-only-user to "web" in import!q2 everything working fine. Thats an issue with "-"
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40363769
Got it! (I think)

Change

=IF(IMPORT!Q2="web-only-user";

to =IF(IMPORT!Q2="web-only-user",

It should be a comma, not a semi-colon.

Trouble is, your initial formula won't actually work, so if this doesn't work, we are a little in the dark helping you. So please then post your spreadsheet.
0
 
LVL 2

Author Comment

by:Mandy_
ID: 40363779
if i change q2 and type web-only-user it's also working. The data in this column are coming from powershell.

and if we search in the formula for "web" in q2?
0
 
LVL 12

Expert Comment

by:James Elliott
ID: 40363782
I doubt the semi-colon is the issue. You can change the standard delimiter and some regions have the semi colon as standard.

I refer to my previous post. The issue looks like the ascii character used to do the hyphen (or the minus). There are at least two different possibility within the standard ascii set.
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40363783
Please post your spreadsheet. There's nothing more I can help you with unless you do so.
0
 
LVL 12

Expert Comment

by:James Elliott
ID: 40363790
When typing your formula, instead of hitting the hyphen, instead hit ALT+196 and see if that works.
0
 
LVL 2

Author Closing Comment

by:Mandy_
ID: 40364177
My fault!  the CSV was written in ACSII Encoding. I changed to default and now all working fine.

Thank you so much for your help!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

664 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question