How to use "OR" when having several IF statements

Murz

Hi,

I posted a question earlier today so this is similar but maybe a different way of going about the solution I'm looking for.

I am trying to figure out how to use "OR" when having 3+ actions in a single formula based on if a field is blank or not.

My scenario is this:

	ORG Code 1	Object Code 1a	Object Code 1b	Object Code 1c	Object Code 1d	ORG Code 2	Object Code 2a	Object Code 2b	Object Code 2c	Object Code 2d	ORG Code 3	Object Code 3a	Object Code 3b	Object Code 3c	Object Code 3d
User 1	ABC	Test 1	Value 2			XYZ	Value 3				DEF	Test 2	Test 3	Test 4
User 2	XYZ	Value 1	Value 3	Value 4		DEF	Test 5	Test 6

I have users who will be entering variable amounts of data but sequential. For example, you can only enter an Object Code if the ORG Code is entered. Your Object Codes must be sequential (Object Code 1a must be before Object Code 1b which must be before Object Code 1c, etc). If an ORG code is entered, there must be at least Object Code Xa entered.

I need to list out the ORG Code plus the populated Object Codes for each user, have a delimiter of some sort (comma?) and skip blanks.

So User 1 would be

ABC, Test 1, Value 2, XYZ, Value 3, DEF, Test 2, Test 3, Test 4

and User 2 would be

XYZ, Value 1, Value 3, Value 4, DEF, Test 5, Test 6

I have had a suggestion to use JOIN(COLLECT which could work except for that the Object Codes and Org Codes are not adjacent and need to remain this way due to the way I have my sheet set up.

So I'm trying a series of IF statements that would list only values and ignore blanks, but I'm having trouble with how to make it do "OR" statements since each time this runs, it needs to take into account which fields are populated and which are blank, since none will be the same.

I've tried something likek this:

=IF(OR([Object Code 1b]@row="","",JOIN([Object Code 1a]@row+[Object Code 2a]@row, IF(OR([Object Code 1c]@row = "", "", JOIN([Object Code 1a]@row + ", " + [Object Code 1b]@row + ", " + [Object Code 1c]@row))), IF(OR([Object Code 1d]@row = "", "", JOIN([Object Code 1a]@row + [Object Code 1b]@row + [Object Code 1c]@row + [Object Code 1d]@row)

I'm really struggling with the IF(OR) because I don't think it work that way. But I'm stumped as to how I can have smartsheet only choose the valid IF statement for the field that has a value in it.

Am I missing a function? Can I use helper columns? In my Smartsheet, I have potentiall 7 ORG codes with up to 4 Object Codes for each so it it a bunch of information.

Thank you so much!

Jgorsich

Join(collect()) is still your best bet, you just need to filter for blanks.

=JOIN(COLLECT([org Code 1]@row:[Object Code 3d]@row, [org Code 1]@row:[Object Code 3d]@row, <>""),",")

should work for you. In the collect() you tell it to only collect things that aren't blank (<>"").

If - btw - your statement earlier that join(collect()) doesn't work because they aren't adjacent means you have extra columns in between that you are trying to dodge, you can do multiple join(collect())s and just concatenate them. If it is too complicated for that, you might want to consider a more illuminating screenshot.