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

Egvern

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!

Leibel S

@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)

Leibel S

@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)

BullandKhmer

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

Egvern

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!

Leibel S

Nope. There is no Sort function in Smartsheet