Calculation - listing values from several fields
I can't seem to figure out a way to make a field that looks at several other fields and gives me their values in a comma sorted list, while omiting one from the value set.
Here is the example:
I want a field that looks at these four values and omits the one I choose. for example the calculation would give me "blue, green, orange" if I choose to omit "red." (in my case, I will always want to remove "red" but this color may be in any of the 4 fields)
The calculation as I have it written now, is surely not the best way to do this. Here is what I have:
If ( color_1 = "red" ; "" ; color_1 ) & If ( color_2 = "red" ; "" ; color_2 ) & If ( color_3 = "red" ; "" ; color_3 ) & If ( color_4 = "red" ; "" ; color_4 )
This gives me what I want, but they are listed together "bluegreenorange" like that.
Any ideas as to how to get what I need?
Thanks a bunch