?
Solved

IF(ISERROR(SEARCH....  correct formula

Posted on 2014-09-21
11
Medium Priority
?
180 Views
Last Modified: 2014-09-26
Hi experts,
could anybody check the formula it makes me headache.  it should insert the -identity "&A3))  at the end of all 3 variations but only working with GUL. Thanks in advance

 =Set-Mailbox  -CustomAttribute4 '"&IF(ISERROR(SEARCH("GUL",S3)),IF(ISERROR(SEARCH("GAL",M3)),R3&"' -CustomAttribute10 '"&T3&"' -CustomAttribute14 'uc' -EmailAddressPolicyEnabled $false -SingleItemRecoveryEnabled $true -EmailAddresses '"&V3&"','"&W3&"','"&X3&"','SMTP:"&J3,"S' -CustomAttribute10 'LSU' -CustomAttribute14 'uc' -EmailAddressPolicyEnabled $false -SingleItemRecoveryEnabled $true -EmailAddresses '"&V3&"','"&W3&"','"&X3&"','SMTP:"&J3&"','smtp:"&K3),"S' -CustomAttribute8 'LUS' -CustomAttribute10 'LUS' -CustomAttribute14 'uc' -EmailAddressPolicyEnabled $false -SingleItemRecoveryEnabled $true -EmailAddresses '"&V3&"','"&W3&"','"&X3&"','SMTP:"&J3&"','smtp:"&K3&"','smtp:"&L3&"' -Identity "&A3))



=Set-Mailbox  -CustomAttribute4 '"&IF(ISERROR(SEARCH("GUL",S3)),IF(ISERROR(SEARCH("GAL",M3)),R3&"' -CustomAttribute10 '"&T3&"' -CustomAttribute14 'uc' -EmailAddressPolicyEnabled $false -SingleItemRecoveryEnabled $true -EmailAddresses '"&V3&"','"&W3&"','"&X3&"','SMTP:"&J3,"S' -CustomAttribute10 'LSU' -CustomAttribute14 'uc' -EmailAddressPolicyEnabled $false -SingleItemRecoveryEnabled $true -EmailAddresses '"&V3&"','"&W3&"','"&X3&"','SMTP:"&J3&"','smtp:"&K3),"S' -CustomAttribute8 'LUS' -CustomAttribute10 'LUS' -CustomAttribute14 'uc' -EmailAddressPolicyEnabled $false -SingleItemRecoveryEnabled $true -EmailAddresses '"&V3&"','"&W3&"','"&X3&"','SMTP:"&J3&"','smtp:"&K3&"','smtp:"&L3&"' -Identity "&A3))

Open in new window

Regards
Mandy
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
  • 7
  • 2
  • 2
11 Comments
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40336094
i think, it would be good to upload the sample file, to be able to detect the problem
0
 
LVL 2

Author Comment

by:Mandy_
ID: 40336112
hi,

now i replace the -identity "&A3 from end to beginning of the formula.

=Set-Mailbox  identity "&A3&" -CustomAttribute4....

and now every variation has included -identity but new problem occurs missing  ' at the end of 2 formula
The ' has to be insert at 2 places.  

'SMTP:"&J3,"S'   result should be 'mike.jones@domain.com' now its 'mike.jones@domain.com

'smtp:"&K3),"S'   here the same

pls see attached the example
ee-setmbx-example.xlsx
0
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40336329
i could not understand what is exactly required.

you mentioned in your earlier comment that "only working with GUL"
in your attached workbook, i did not see any working formula.  the formula has lot of missing qoutes to refer to the text.
perhaps if you could only indicate the one working with GUL then i might be able to understand what is needed.
0
Need protection from advanced malware attacks?

Look no further than WatchGuard's Total Security Suite, providing defense in depth against today's most headlining attacks like Petya 2.0 and WannaCry. Keep your organization out of the news with protection from known and unknown threats.

 
LVL 2

Author Comment

by:Mandy_
ID: 40337019
Dear experts,

soory. pls see the attachment with working formula for GUL in F26

Thank you
Mandy
ee-setmbx-example.xlsx
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 2000 total points
ID: 40337067
Mandy,

It appears that you're trying to generate a script in Exchange Management Shell to configure email forwarding, correct?

If this is true, then you have to treat all components as substrings and concatentate them together so that the result is also a string that you can then copy and paste into EMS.  This is probably where the single and double quotes are getting lost in your current formula.

My suggestion is that you display what the final EMS script should look like given the data presented in row 3 so that we can use that as a reference.  Then state what changes you expect if the values in M3 or S3 are anything other than "GAL" or "GUL".

I will state at the onset that I think this may be the test to start:
="=Set-Mailbox  -CustomAttribute4 '" & IF(OR(UPPER(S3)="GUL",UPPER(M3)="GAL"), ...build script...,R3)
0
 
LVL 2

Author Comment

by:Mandy_
ID: 40337135
Glenn,

you're right! The formula building the powershell code to paste into EMS. You could paste multiple rows
with formula at one time with short delay between.

GUL
-CustomAttribute4 's' -CustomAttribute8 'LUS' -CustomAttribute10 'LUS' -CustomAttribute14 'uc' -EmailAddressPolicyEnabled $false -SingleItemRecoveryEnabled $true -EmailAddresses '"&V3&"','"&W3&"','"&X3&"','SMTP:"&J3&"','smtp:"&K3&"','smtp:"&L3&"' -Identity "&A3))

GAL
-CustomAttrbute4 'S' -CustomAttribute10 'LSU' -CustomAttribute14 'uc' -EmailAddressPolicyEnabled $false -SingleItemRecoveryEnabled $true -EmailAddresses '"&V3&"','"&W3&"','"&X3&"','SMTP:"&J3&"','smtp:"&K3) -Identity "&A3))

other
-CustomAttribute4 "&R3&" -CustomAttribute10 '"&T3&"' -CustomAttribute14 'uc' -EmailAddressPolicyEnabled $false -SingleItemRecoveryEnabled $true -EmailAddresses '"&V3&"','"&W3&"','"&X3&"','SMTP:"&J3 -Identity "&A3))


Thanks
mandy
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40337161
Okay, this is much better.  Can you clarify these questions?

1) What values can cell M3 be?  (ex., "gal", "GAL", "Gal", blank, other?)
2) What values can cell S3 be? (ex., "gul", "GUL", "Gul", blank, other?)
3) If cells M3 and S3 both have values, can they be different?  If so, which one takes precedence?

-Glenn
0
 
LVL 2

Author Comment

by:Mandy_
ID: 40337328
I think   IF(ISERROR(SEARCH  is required.

1. M3 could be GAL, GALSD , GALESDA or different Versions which include GAL and all other  not blank
2. S3 only has only one value GUL not blank
3.Usually not both have values


M3 is the company short Code and for GAL* has to set primary smtp gal.com and 2nd e.g. @gil.com
and special custom attributes - see again below
If M3 not GAL* only has to set one primary smtp and different custom attributes - see again below


S3 is unique always one value "GUL"  is custom Attribute 8  only set for this company. 3 special smtp and
special custom attributes

GUL
-CustomAttribute4 's' -CustomAttribute8 'LUS' -CustomAttribute10 'LUS' -CustomAttribute14 'uc' -EmailAddressPolicyEnabled $false -SingleItemRecoveryEnabled $true -EmailAddresses '"&V3&"','"&W3&"','"&X3&"','SMTP:"&J3&"','smtp:"&K3&"','smtp:"&L3&"' -Identity "&A3))

GAL
-CustomAttrbute4 'S' -CustomAttribute10 'LSU' -CustomAttribute14 'uc' -EmailAddressPolicyEnabled $false -SingleItemRecoveryEnabled $true -EmailAddresses '"&V3&"','"&W3&"','"&X3&"','SMTP:"&J3&"','smtp:"&K3) -Identity "&A3))

other
-CustomAttribute4 "&R3&" -CustomAttribute10 '"&T3&"' -CustomAttribute14 'uc' -EmailAddressPolicyEnabled $false -SingleItemRecoveryEnabled $true -EmailAddresses '"&V3&"','"&W3&"','"&X3&"','SMTP:"&J3 -Identity "&A3))

it's complicated. sorry for that

After replace  of -identity at the beginning like this
Set-Mailbox  -identity "&A3&" -CustomAttribute4..... only an apostrophe
is missing for GAL and other at the end .

'SMTP:"&J3,"S'   result should be 'mike.jones@domain.com' now its 'mike.jones@domain.com

'smtp:"&K3),"S'   here the same
0
 
LVL 2

Author Comment

by:Mandy_
ID: 40339220
Dear experts,

everything is resolved. Gracias Dios!


="Start-Sleep -s 10;Set-Mailbox  -CustomAttribute4 '"&IF(ISERROR(SEARCH("GAL";L2));IF(ISERROR(SEARCH("GUL";L2));P2&"' -CustomAttribute10 'LT' -CustomAttribute14 'HAD' -EmailAddressPolicyEnabled $false -SingleItemRecoveryEnabled $true -EmailAddresses '"&I2&"';'"&J2&"';'"&K2&"';'SMTP:"&F2&"';'smtp:"&G2,"T' -CustomAttribute10 'LTS' -CustomAttribute14 'HAD' -EmailAddressPolicyEnabled $false -SingleItemRecoveryEnabled $true -EmailAddresses '"&I2&"';'"&J2&"';'"&K2&"';'SMTP:"&F2&"';'smtp:"&G2),"T' -CustomAttribute8 'LS' -CustomAttribute10 'LS' -CustomAttribute14 'HAD' -EmailAddressPolicyEnabled $false -SingleItemRecoveryEnabled $true -EmailAddresses '"&I2&"';'"&J2&"';'"&K2&"';'SMTP:"&F2&"';'smtp:"&G2&"';'smtp:"&G2)&"' -Identity "&A2
0
 
LVL 2

Author Comment

by:Mandy_
ID: 40346561
I've requested that this question be closed as follows:

Accepted answer: 0 points for Mandy_'s comment #a40339220

for the following reason:

reconstructed
0
 
LVL 2

Author Closing Comment

by:Mandy_
ID: 40346562
fine thank you
0

Featured Post

Four New Appliances. Same Industry-leading Speeds.

But don't take it from us.  The Firebox M370 is Miercom tested and Miercom approved, outperforming its competitors for stateless and stateful traffic throughput scenarios.  Learn more about the M370, M470, M570 and M670 and find the right solution for your organization today!

Question has a verified solution.

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

Had a business requirement to store the mobile number in an environmental variable. This is just a quick article on how this was done.
Active Directory can easily get cluttered with unused service, user and computer accounts. In this article, I will show you the way I like to implement ADCleanup..
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Suggested Courses

777 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