IF(ISERROR(SEARCH.... correct formula

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
LVL 2
Mandy_Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ProfessorJimJamCommented:
i think, it would be good to upload the sample file, to be able to detect the problem
0
Mandy_Author Commented:
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
ProfessorJimJamCommented:
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
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Mandy_Author Commented:
Dear experts,

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

Thank you
Mandy
ee-setmbx-example.xlsx
0
Glenn RayExcel VBA DeveloperCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mandy_Author Commented:
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
Glenn RayExcel VBA DeveloperCommented:
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
Mandy_Author Commented:
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
Mandy_Author Commented:
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
Mandy_Author Commented:
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
Mandy_Author Commented:
fine thank you
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.