×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# Excel -Assign value to field combinations

 Forum Search FAQs Links MVPs

## Excel -Assign value to field combinations

(OP)
I am using Excel 2016 and trying to figure out how to assign values to field combinations with a formula. Looking at status of equipment (Operation and Admin)
Based on unique query there are 3 possible combinations:

### RE: Excel -Assign value to field combinations

Sure you can do a long formula, something like: =If(AND(if(and(.... and on and on, but I would create my own formula and use that.

Alt-F11 (VBA Editor), add a standard Module and paste this code:

#### CODE

Option Explicit

Function Freefall69(ByRef str1 As String, ByRef str2 As String) As String
Dim strOut As String

If str1 = "up" And str2 = "up" Then
strOut = "Active"
ElseIf (str1 = "down" And str2 = "up") Or (str1 = "up" And str2 = "down") Then
strOut = "Available"
ElseIf str1 = "down" And str2 = "down" Then
strOut = "Not Active"
Else
strOut = "What is that?"
End If

Freefall69 = strOut

End Function 

And if you have in your Excel "up" and "down" in cells A2 and B2, in cell C2 you can enter:
=Freefall69(A2,B2)

---- Andy

There is a great need for a sarcasm font.

### RE: Excel -Assign value to field combinations

(OP)
Andy,
Thanks! That works perfectly!!

### RE: Excel -Assign value to field combinations

Freefall69,
If the answer works for you, consider clicking the Great post! link to let other know the question has been resolved.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

### RE: Excel -Assign value to field combinations

Great!
This is the power of your own VBA function. Easy to see, easy to add or modify.
You could write it like this in cell C3:

=IF(AND(A2 = "up", B2 = "up"), "Active", IF(AND(A2 = "up", B2 = "down"), "Available", IF(AND(A2 = "down", B2 = "down"), "Not Active", "What?")))

but that's not fun....

---- Andy

There is a great need for a sarcasm font.

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

#### Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!