Looking for a formula which will look at a series of cells but only return the information in one.

damon.tackett

I have a series of columns based on job functions. Only one of the cells will have information and the rest will be blank. I am looking for a formula that will look at these cells and only return the information in the cell that has information in it. This will be combined with the information in two other cells to form a "Request Name" in the Primary column. The High Level Management column is a drop down and depending on the function one of the following columns will be populated.

what i have currently is =Subject@row + " + " + [High Level Function]@row+" + " but I can't get a handle on how to make it search the rest. Referencing the example below I would like the result to read:

American Chevrolet + Dealer Record Management + Dealer Reactivation

Leibel S

@damon.tackett

Add to your formula the below:

=Subject@row + " + " + [High Level Function]@row+" + " + INDEX(COLLECT([Admin system Management]@row:[Rates Management]@row, [Admin system Management]@row:[Rates Management]@row, @cell <> ""), 1, 1)

Please note this requires that the columns you are looking at will always be between 'Admin system Management' and 'Rates Management' columns (so the order of columns should not be changed).

Leibel S

@damon.tackett

Add to your formula the below:

=Subject@row + " + " + [High Level Function]@row+" + " + INDEX(COLLECT([Admin system Management]@row:[Rates Management]@row, [Admin system Management]@row:[Rates Management]@row, @cell <> ""), 1, 1)

Please note this requires that the columns you are looking at will always be between 'Admin system Management' and 'Rates Management' columns (so the order of columns should not be changed).

damon.tackett

@Leibel Shuchat

Thank you! That is perfect for what I am wanting to do. I actually needed to expand it a few columns for other categories but was able to do that easily enough once I saw the logic.

I am pretty new to Smartsheets and formulas. This community is a saving grace for sure!