asked on

Excel complete formula depends on the cell is filled or not otherwise use other cell

dear experts,

could anybody help me with this one. I like to fill this command below depending on Excel-Cells. So far so good.
The formula should check if the cell C9 has a value (email adress) and if not use CELL C8 to complete the
command. If C9 has a value use this if not use C8

Thanks in advance

CELL C8 test@test.com
CELL C9 jones@gal.com

="Set-Mailbox -EmailAddresses 'SMTP:"&C8&"' -SingleItemRecoveryEnabled $true -EmailAddressPolicyEnabled $false -Displayname '"&B5&"'

Open in new window

Ejgil Hedegaard

Use IF(LEN(C9),C9,C8) for C8 in the formula, like this

="Set-Mailbox -EmailAddresses 'SMTP:"&IF(LEN(C9),C9,C8)&"' -SingleItemRecoveryEnabled $true -EmailAddressPolicyEnabled $false -Displayname '"&B5&"'"

Open in new window

Mandy_

ASKER

Thanks. I'm getting #name?

Mandy_

ASKER

hi

if i use the string below i'm getting #name?
I dont know what is wrong

="Set-Mailbox -identity "jones, robert" -EmailAddresses 'SMTP:"&IF(LEN(C9),C9,C8)&"'"

Ejgil Hedegaard

jones, robert is between the 2 texts "Set-Mailbox -identity " and " -EmailAddresses 'SMTP:" without & to join.
It should be
="Set-Mailbox -identity "&"jones, robert"&" -EmailAddresses 'SMTP:"&IF(LEN(C9),C9,C8)&"'"
or
="Set-Mailbox -identity jones, robert -EmailAddresses 'SMTP:"&IF(LEN(C9),C9,C8)&"'"

Strange you can enter it.

Mandy_

ASKER

Still the same. #name?
to join the name is not needed. I put the formula ="Set-Mailbox -EmailAddresses 'SMTP:"&IF(LEN(C9),C9,C8)&"'"
in cell C10 and it should resolve from the CELL C9 (123@test.com) like 'SMTP:123@test.com‘ or if the CELL C9 is
empty from Cell C8. The result should be

Set-Mailbox -EmailAddresses 'SMTP:123@test.com‘ or Set-Mailbox -EmailAddresses SMTP:456@test.com‘
if the cell C9 is empty and C8 is 456@test.com.

Ejgil Hedegaard

The error #name? means Excel can't recognise the used function, and 'think' it is a named range, and that name don't exist.
Are you using an English version of Excel?

Try typing the formula =IF(LEN(C9),C9,C8) in an empty cell.
When you type =i a list with available functions starting with i are shown, and if IF is not on the list, that is the problem.

ASKER CERTIFIED SOLUTION

Ejgil Hedegaard

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mandy_

ASKER

thank you so much