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!

Find more posts tagged with

Cell linking

Join

collect

Accepted answers

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)

All comments

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.