[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
Medium Priority
206 Views
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

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

``````
0
Question by:Mandy_
• 4
• 4

LVL 24

Expert Comment

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&"'"
``````
0

LVL 2

Author Comment

ID: 40281526
Thanks. I'm getting #name?
0

LVL 2

Author Comment

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

LVL 24

Expert Comment

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

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

if the cell C9 is empty and C8 is 456@test.com.
0

LVL 24

Expert Comment

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

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

LVL 2

Author Closing Comment

ID: 40283525
thank you so much
0

## Featured Post

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.
###### Suggested Courses
Course of the Month20 days, 10 hours left to enroll