Trying to write a formula that will recognise a word value in one field then if one or more word values are meet in the second field then it returns a value in a third field. This needs to be able to meet several values in both the first and second fields and return a different value in the third field depending on the condition of the first and second fields.
Can we discuss in details for this ?
Yes please would love to be able to get this sorted if it is possible
+3@Brian_Masters sounds like you could use SWITCH logic depending on how many options you have in the first or second field:
SWITCH(field1 & ':' & field2,
'field1word1:field2word1', 'field3option1',
'field1word1:field2word2', 'field3option2',
'field1word1:field2word3', 'field3option3',
'field1word2:field2word1', 'field3option4',
'field1word2:field2word2', 'field3option5',
'field1word2:field2word3', 'field3option6'
)
This would work well if each combination led to a unique outcome. Otherwise, it’d work to use nested IF statements. Let me know if this project runs into any issues I’d be happy to help.
@Brian_Masters sounds like you could use SWITCH logic depending on how many options you have in the first or second field:
SWITCH(field1 & ':' & field2,
'field1word1:field2word1', 'field3option1',
'field1word1:field2word2', 'field3option2',
'field1word1:field2word3', 'field3option3',
'field1word2:field2word1', 'field3option4',
'field1word2:field2word2', 'field3option5',
'field1word2:field2word3', 'field3option6'
)
This would work well if each combination led to a unique outcome. Otherwise, it’d work to use nested IF statements. Let me know if this project runs into any issues I’d be happy to help.
There are 5 option in both 1 and 2 fields giving 25 possible outcomes, however some of the outcomes are the same for each combination option of field 1 and 2.
I will give it a go and see if it works. I have tried to us a nested IF statement however it would not return the values I selected. It might have been the way I set the formula up.
Thanks for the advice and I will let you know h ok w I go.
@Brian_Masters sounds like you could use SWITCH logic depending on how many options you have in the first or second field:
SWITCH(field1 & ':' & field2,
'field1word1:field2word1', 'field3option1',
'field1word1:field2word2', 'field3option2',
'field1word1:field2word3', 'field3option3',
'field1word2:field2word1', 'field3option4',
'field1word2:field2word2', 'field3option5',
'field1word2:field2word3', 'field3option6'
)
This would work well if each combination led to a unique outcome. Otherwise, it’d work to use nested IF statements. Let me know if this project runs into any issues I’d be happy to help.
Thank you for the information on the formula, it works well with what I was trying to accomplish.
Regards,
Brian Masters
Managing director
Muddle’s Place
P: 03 6705 7070
M: 0447359 335
+18Thank you for the information on the formula, it works well with what I was trying to accomplish.
Regards,
Brian Masters
Managing director
Muddle’s Place
P: 03 6705 7070
M: 0447359 335
Hi @Brian_Masters - as an alternative you could also try using a reference table. The problem I find with complicated formulas is that they are easy to get wrong and difficult to debug. So, you could create a table that references the two choices and the result. In my example below, I’m choosing a client and a service to provide and the combination of these two determines who will do the work.
I’ve created the primary key of the table to be the combination of the client and service (using CONCATENATE).
I also have a projects table:
Here, the client/service field is a link field to the client service table. The Person field is a lookup on the same table, i.e. choosing “Apple - Development” automatically looks up Jim.
JB
Hi @Brian_Masters - as an alternative you could also try using a reference table. The problem I find with complicated formulas is that they are easy to get wrong and difficult to debug. So, you could create a table that references the two choices and the result. In my example below, I’m choosing a client and a service to provide and the combination of these two determines who will do the work.
I’ve created the primary key of the table to be the combination of the client and service (using CONCATENATE).
I also have a projects table:
Here, the client/service field is a link field to the client service table. The Person field is a lookup on the same table, i.e. choosing “Apple - Development” automatically looks up Jim.
JB
Thank you Jonathan,
I used a formula for what I needed and it is working for me at the moment. My fields are in the middle of the table so a little more difficult to work with.
Appreciate your time and suggestion will keep this in mine for few other tables I’m working on.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.