[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2014-08-24
8
Medium Priority
?
206 Views
Last Modified: 2014-08-25
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

0
Comment
Question by:Mandy_
  • 4
  • 4
8 Comments
 
LVL 24

Expert Comment

by:Ejgil Hedegaard
ID: 40281433
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

0
 
LVL 2

Author Comment

by:Mandy_
ID: 40281526
Thanks. I'm getting #name?
0
 
LVL 2

Author Comment

by:Mandy_
ID: 40281535
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)&"'"
0
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!

 
LVL 24

Expert Comment

by:Ejgil Hedegaard
ID: 40281740
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.
0
 
LVL 2

Author Comment

by:Mandy_
ID: 40281775
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.
0
 
LVL 24

Expert Comment

by:Ejgil Hedegaard
ID: 40281922
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.
0
 
LVL 24

Accepted Solution

by:
Ejgil Hedegaard earned 2000 total points
ID: 40281929
See sheet with the formula.
Foemula-problem.xlsx
0
 
LVL 2

Author Closing Comment

by:Mandy_
ID: 40283525
thank you so much
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

867 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