×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*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.

Students Click Here

Jobs

Excel -Assign value to field combinations

Excel -Assign value to field combinations

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! thumbsup2
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.

Reply To This Thread

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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close