Formula to summarize orders of 1-10 products within 1 column using info from other columns

Tammy D

"Trying to create a summary column that lists (on a separate line) the 'order' for shipping of 1 to 10 items. Ideally, if 10 items, it lists all ten (item description,# & units) on a separate line if 10 items are requested, if only 1 requested, then it only lists 1. I have done an If nested statement that worked but when I try to input the formula, I run out of space in the formula(are there limits on formula size?) Here is my first part --need to repeat for the next 9 but there has to be an easier way. . . HELP!

=IF(ISTEXT([10. Item Code/Description]@row), "1. " + [1. Item Code/Description]@row + "- " + [1. Quantity to Ship]@row + " " + [1. Unit]@row + CHAR(10) + "2. " + [2. Item Code/Description]@row + "- " + [2. Quantity to Ship]@row + " " + [2. Unit]@row + CHAR(10) + "3. " + [3. Item Code/Description]@row + "- " + [3. Quantity to Ship]@row + " " + [3. Unit]@row + CHAR(10) + "4. " + [4. Item Code/Description]@row + "- " + [4. Quantity to Ship]@row + " " + [4. Unit]@row + CHAR(10) + "5. " + [5. Item Code/Description]@row + "- " + [5. Quantity to Ship]@row + " " + [5. Unit]@row + CHAR(10) + "6. " + [6. Item Code/Description]@row + "- " + [6. Quantity to Ship]@row + " " + [6. Unit]@row + CHAR(10) + "7. " + [7. Item Code/Description]@row + "- " + [7. Quantity to Ship]@row + " " + [7. Unit]@row + CHAR(10) + "8. " + [8. Item Code/Description]@row + "- " + [8. Quantity to Ship]@row + " " + [8. Unit]@row + CHAR(10) + "9. " + [9. Item Code/Description]@row + "- " + [9. Quantity to Ship]@row + " " + [9. Unit]@row + CHAR(10) + "10. " + [10. Item Code/Description]@row + "- " + [10. Quantity to Ship]@row + " " + [10. Unit]@row, "")"

Matt Lynn-PCG

@Tammy D Yes there's a 4000 character in a formula / cell. I have done this several times and you may need to do a separate helper column per product then use a simpler formula in a final helper column to if(then those into one.

I don't think you'll be in risk here but just also keep in mind your cell references limits. I've done this before with no problems but also done it where I exceeded limitations due to rows. I think the limit a sheet can reference itself or others in a cell is 25m times. So just for example this formula you show above references itself 22 times roughly. If that's for one product then the 10 products would have 220 references. So on 1,000 rows it would reference itself 220k times. That's still not much so I doubt you'd run into any issues. I just simply offer it as a warning for scale depending on what the rest of the sheet is doing. This limit is usually hit when you have big formulas like this that reference entire columns rather than @row. If it were entire columns that adds up much faster. Example of that… if just one of these @row references was a column instead (still assuming 1000 rows) that 22k would jump to over 1m.

Anyway… sorry that was a $5 answer for a $0.50 question.

Matt Lynn-PCG

@Tammy D Yes there's a 4000 character in a formula / cell. I have done this several times and you may need to do a separate helper column per product then use a simpler formula in a final helper column to if(then those into one.

I don't think you'll be in risk here but just also keep in mind your cell references limits. I've done this before with no problems but also done it where I exceeded limitations due to rows. I think the limit a sheet can reference itself or others in a cell is 25m times. So just for example this formula you show above references itself 22 times roughly. If that's for one product then the 10 products would have 220 references. So on 1,000 rows it would reference itself 220k times. That's still not much so I doubt you'd run into any issues. I just simply offer it as a warning for scale depending on what the rest of the sheet is doing. This limit is usually hit when you have big formulas like this that reference entire columns rather than @row. If it were entire columns that adds up much faster. Example of that… if just one of these @row references was a column instead (still assuming 1000 rows) that 22k would jump to over 1m.

Anyway… sorry that was a $5 answer for a $0.50 question.

Humashankar

That's useful information

Tammy D

@Matt Lynn-PCG Thank you so much! I did create summary columns to get to the solution — glad to see my thought process was on the right track and also sorry to see not an easier way to do it! Appreciate the $5.00 answer for $0.50 —not info I could find on the help site easily. I'm just beginning my SmartSheet journey and it is one big puzzle to figure out!