Mandy_
asked on
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(I SERROR(SEA RCH("GAL", M3)),R3&"' -CustomAttribute10 '"&T3&"' -CustomAttribute14 'uc' -EmailAddressPolicyEnabled $false -SingleItemRecoveryEnabled $true -EmailAddresses '"&V3&"','"&W3&"','"&X3&"' ,'SMTP:"&J 3,"S' -CustomAttribute10 'LSU' -CustomAttribute14 'uc' -EmailAddressPolicyEnabled $false -SingleItemRecoveryEnabled $true -EmailAddresses '"&V3&"','"&W3&"','"&X3&"' ,'SMTP:"&J 3&"','smtp :"&K3),"S' -CustomAttribute8 'LUS' -CustomAttribute10 'LUS' -CustomAttribute14 'uc' -EmailAddressPolicyEnabled $false -SingleItemRecoveryEnabled $true -EmailAddresses '"&V3&"','"&W3&"','"&X3&"' ,'SMTP:"&J 3&"','smtp :"&K3&"',' smtp:"&L3& "' -Identity "&A3))
Mandy
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"
=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))
RegardsMandy
i think, it would be good to upload the sample file, to be able to detect the problem
ASKER
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
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
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.
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.
ASKER
Dear experts,
soory. pls see the attachment with working formula for GUL in F26
Thank you
Mandy
ee-setmbx-example.xlsx
soory. pls see the attachment with working formula for GUL in F26
Thank you
Mandy
ee-setmbx-example.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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:"&J 3&"','smtp :"&K3&"',' smtp:"&L3& "' -Identity "&A3))
GAL
-CustomAttrbute4 'S' -CustomAttribute10 'LSU' -CustomAttribute14 'uc' -EmailAddressPolicyEnabled $false -SingleItemRecoveryEnabled $true -EmailAddresses '"&V3&"','"&W3&"','"&X3&"' ,'SMTP:"&J 3&"','smtp :"&K3) -Identity "&A3))
other
-CustomAttribute4 "&R3&" -CustomAttribute10 '"&T3&"' -CustomAttribute14 'uc' -EmailAddressPolicyEnabled $false -SingleItemRecoveryEnabled $true -EmailAddresses '"&V3&"','"&W3&"','"&X3&"' ,'SMTP:"&J 3 -Identity "&A3))
Thanks
mandy
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
GAL
-CustomAttrbute4 'S' -CustomAttribute10 'LSU' -CustomAttribute14 'uc' -EmailAddressPolicyEnabled
other
-CustomAttribute4 "&R3&" -CustomAttribute10 '"&T3&"' -CustomAttribute14 'uc' -EmailAddressPolicyEnabled
Thanks
mandy
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
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
ASKER
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
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
GAL
-CustomAttrbute4 'S' -CustomAttribute10 'LSU' -CustomAttribute14 'uc' -EmailAddressPolicyEnabled
other
-CustomAttribute4 "&R3&" -CustomAttribute10 '"&T3&"' -CustomAttribute14 'uc' -EmailAddressPolicyEnabled
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
ASKER
Dear experts,
everything is resolved. Gracias Dios!
="Start-Sleep -s 10;Set-Mailbox -CustomAttribute4 '"&IF(ISERROR(SEARCH("GAL" ;L2));IF(I SERROR(SEA RCH("GUL"; L2));P2&"' -CustomAttribute10 'LT' -CustomAttribute14 'HAD' -EmailAddressPolicyEnabled $false -SingleItemRecoveryEnabled $true -EmailAddresses '"&I2&"';'"&J2&"';'"&K2&"' ;'SMTP:"&F 2&"';'smtp :"&G2,"T' -CustomAttribute10 'LTS' -CustomAttribute14 'HAD' -EmailAddressPolicyEnabled $false -SingleItemRecoveryEnabled $true -EmailAddresses '"&I2&"';'"&J2&"';'"&K2&"' ;'SMTP:"&F 2&"';'smtp :"&G2),"T' -CustomAttribute8 'LS' -CustomAttribute10 'LS' -CustomAttribute14 'HAD' -EmailAddressPolicyEnabled $false -SingleItemRecoveryEnabled $true -EmailAddresses '"&I2&"';'"&J2&"';'"&K2&"' ;'SMTP:"&F 2&"';'smtp :"&G2&"';' smtp:"&G2) &"' -Identity "&A2
everything is resolved. Gracias Dios!
="Start-Sleep -s 10;Set-Mailbox -CustomAttribute4 '"&IF(ISERROR(SEARCH("GAL"
ASKER
I've requested that this question be closed as follows:
Accepted answer: 0 points for Mandy_'s comment #a40339220
for the following reason:
reconstructed
Accepted answer: 0 points for Mandy_'s comment #a40339220
for the following reason:
reconstructed
ASKER
fine thank you