FORMULA WHEN THERE ARE MULTIPLE LINES IN A CELL

chetg2000

I have a sheet that has some rows that have multiple lines. I want to use the qty from each line for a summary formula. I have a formula that works when there are only one line in a row, but I get an error when there are multiple lines. Anyone know of a way to do this?

Dan Beres

Hi @chetg2000

I am assuming that you are using alt+enter or some other way to insert a paragraph line into these cells? If you are trying to SUM those quantities, I don't think there is a very straightforward way to do that since the information will now be saved as a text string instead of a number. This means the formula won't be able to read/calculate it properly.

The most straightforward way is to just have each line item in the order in its own row, with the order # being the same across multiple rows.

A potential way around this if you still want the data to present itself in the same way, is that you have an alternative sheet that has all of the information stored line by line. You would have 7 different rows for this order, but you would still have the order #64560 for each one of them. You would then have to find some way to combine them from the other sheet with something like:

=JOIN(COLLECT(Item Name or whatever you're combining, Order #, CONTAINS([ORDER NUMBER]@row, @cell)), CHAR(10))

This gets more complex and requires Cross-Sheet References. Ultimately it is doing the same thing I stated above where each line item in an order gets its own row in Smartsheet, and then just presenting it in a different way elsewhere so it is all combined.

chetg2000

Ideally, I want the information in the lines to be in separate rows with the same order number. Unfortunately, This is the way the information is being imported. Is there a way to take the information and distribute it to separate rows after it is brought in?