Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

How to return multiple values from a sheet and paste them on multiple cells, based on criteria?

Hi, I have one sheet where there is a column with order numbers. There is also a shipping date column, which has a shipping date for each order number. The sheet itself is called Shipping.

On another sheet, I want to return all order numbers of the next 3 weeks. I tried using the Index(Collect()) formula at first, but unfortunately it returns only one value and not all the values I need:

=INDEX(COLLECT({Shipping Range 2}, {Shipping Range 3}, <TODAY(+21)), 1)

Then, I tried using the JOIN(COLLECT()) formula. It returns the values I need, but it returns them all in a single cell, and I want them over different cells, just like they are in the Shipping sheet:

=JOIN(COLLECT({Shipping Range 2}, {Shipping Range 3}, TODAY(+21)), ",")

I also tried adding CHAR(10) as a delimiter, but it doesn't separate returned values in different cells.

I need some help in this regard! So far, only the report works, but I want something which I can modify and work on it with other formulas as well.

If this is possible, does it update automatically?

Thank you!

Best Answer

  • Community Champion
    Answer ✓

    @Egvern

    Prefill a bunch of rows (however many you think you need) and add a column called ROW# and put in numbers (in order, so 1, 2, 3, 4, etc. 100, etc.)

    Then your formula can change to:

    =INDEX(COLLECT({Shipping Range 2}, {Shipping Range 3}, <TODAY(+21)), [ROW#]@row)

Answers

  • Community Champion
    Answer ✓

    @Egvern

    Prefill a bunch of rows (however many you think you need) and add a column called ROW# and put in numbers (in order, so 1, 2, 3, 4, etc. 100, etc.)

    Then your formula can change to:

    =INDEX(COLLECT({Shipping Range 2}, {Shipping Range 3}, <TODAY(+21)), [ROW#]@row)

  • ✭✭✭✭✭

    Sounds possible, post screen shots of your source a destination.

  • Thank you @Leibel S

    So, if I understand correctly, the COLLECT function creates a "group" of all the values that match the criteria, then the INDEX function returns the values of this group one by one.

    Is there a way to sort the values based by ascending date and return them in that order? When I try to sort the column based on ascending dates, it messes up the ROW# column. I want them to be already in an ascending order after they are collected by the COLLECT function, and then I want them returned in that order through the INDEX function. Thanks!

  • Community Champion

    Nope. There is no Sort function in Smartsheet

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions