0

I have a Sharepoint (2010) list, where I have a column that's supposed to be calculated based on four previous columns.
Each of these columns are selection box columns, each with three options, and you can only select 1 option.

  1. Column 1; option1, option2, option3
  2. Column 2; option1, option2, option3
  3. Column 3; option1, option2, option3
  4. Column 4; option1, option2, option3

Option1, 2, and 3 are all the same value in the different columns.

Now, the calculated column is to be calculated like this:
If all the columns are option 1, the resulting column should be option 1.
If just a single one of the columns are set to option 2, then the calculated column should be option 2.
Finally, if just a single one of the columns are set to option 3, it will supersede the option 2, and the resulting column's calculated outcome should be option 3.

Is this possible to do?

Please note, that the network this is to be done on is not connected to the internet, that Sharepoint Designer is not implemented, nor can it be, and installing scripts are completely out of the question.
Infopath and Access are available, though.

0

I think this calculated column will do it - the long way, but it tests for option 1, then option 3 since it is more important than option 2, then falls through the test for option 2.

=IF(And([Column 1]=”Option 1”,[Column 2]=”Option 1”, [Column 3]=”Option 1”),”Option 1”,

If(
OR(
 AND([Column 1]=”Option 3”,[Column 2]<>”Option 3”, [Column 3]<>”Option 3”),
AND([Column 1]<>”Option 3”,[Column 2]=”Option 3”, [Column 3]<>”Option 3”),
AND([Column 1]<>”Option 3”,[Column 2]<>”Option 3”, [Column 3]=”Option 3”)
), “Option 3”,

If(
OR(
 AND([Column 1]=”Option 2”,[Column 2]<>”Option 2”, [Column 3]<>”Option 2”),
AND([Column 1]<>”Option 2”,[Column 2]=”Option 2”, [Column 3]<>”Option 2”),
AND([Column 1]<>”Option 2”,[Column 2]<>”Option 2”, [Column 3]=”Option 2”)
), “Option 2”,

“Not a Valid Value”
)
)
)

Or you might need to split this out into multiple columns. Maybe have a column that counts the number of options: NumberOption1 (return as number)

=IF([Column 1]="Option 1",1,0)+IF([Column 2]="Option 1",1,0)+IF([Column 3]="Option 1",1,0)+IF([Column 4]="Option 1",1,0)

Then maybe you can simplify the logic by using that field instead of a long AND statement.

As long as you follow the pattern =IF(test,trueaction,falseaction), and build up the true/false actions, keeping track of your commas (or maybe semicolons if you're outside the US) and closing parentheses (use a text editor to split it out into separate lines.

  • Thank you! But when I have two Option 3's or more, it results in 0 - see this: photobox.co.uk/my/photo/full?photo_id=500357008608 . The fourth column, by the way, is also part of the calculation (but from the above, I can add that one myself). – TheKingOfScandinavia Dec 11 '17 at 19:17
  • Ah, that's a bit different than my logic, that might be too complex. Mine assumes columns have to be unique... maybe create other calc fields that count how many columns are using an option, and then a calc field using those. As long as you keep to the structure =IF(test,trueaction,falseaction), you should be able to build up your logic with ANDs and ORs. – Robin in Or Dec 11 '17 at 20:45
  • And for faster dev/testing; use Excel. If you name cells matching your columnnames, you can 1:1 copy your final result to SharePoint. Be aware SP has a maximum of 7 nested calculations – Danny '365CSI' Engelman Dec 12 '17 at 10:19
  • I now have the following formula (formatted for easement of the eyes): pastebin.com/vhQh9QkU But I now hit the 8192 character limit that exists in Sharepoint, but not in Excel (meaning I've confirmed it works) - any ideas as to how to circumvent the character limit? – TheKingOfScandinavia Dec 13 '17 at 14:57
  • About the only way is to somehow split all that into multiple calculated fields - maybe take a logical section and have it return a yes/no - then you could just test against that field. I've had complex scenarios that used 6 different fields - messy but I didn't have to write custom code. – Robin in Or Dec 13 '17 at 17:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.