Is there a way to break out found Rows from a LInked Sheet into sequential rows in a new sheet?

Hi folks. Let's say I have a sheet with the following data:

Then I make a second sheet that links to the first, looking for matches of both the Name and Date. What I want, in this second sheet, is to eventually show that on 12/25, Matt is Eating a Sandwich (first row of new sheet) and Using the Toilet (second row of new sheet). Since there's no time data, it's up to your imagination which order this occurs in.

I know (more or less) how to use Index/Collect to find these two values. But I'm struggling with how to pull them, individually, into separate rows. I don't want a single aggregate row of "Ate a Sandwich, Used the Toilet".

In Filemaker, there's a function (approximately) called GET NTH that allows you to pull specific related values by number (If GET NTH = 1, "Ate a Sandwich", else if GET NTH = 2, "Used the Toilet"). But I'm stalled and can't figure out how to do this same thing in Smartsheets. Help! Help help help!

Find more posts tagged with

Accepted answers

Paul Newcome

In that case, you would insert a text/number column called "Number". This column will be manually populated starting with the number 1 and going until you have as many as you think you will need to accommodate the full list. Using your example, you would need at least three rows, but I would go ahead and extend to ten to provide a buffer just in case.

Then you would use an INDEX/COLLECT along the lines of

=INDEX(COLLECT({Activity}, {Person}, @cell = "Matt", {Date}, @cell = DATE(2026, 12, 25)), Number@row)

All comments

Paul Newcome

What is the reason for having this in a second sheet? Have you looked into a Row Report instead?

FrogHolder_01

@Paul Newcome, I want to use the output in the second sheet as the data in a linked Dropdown List.

So, the Dropdown (in this case, in that same, second sheet) would give:

Ate a Sandwich
Used the Toilet

as the options.

Paul Newcome

Then you would use an INDEX/COLLECT along the lines of

=INDEX(COLLECT({Activity}, {Person}, @cell = "Matt", {Date}, @cell = DATE(2026, 12, 25)), Number@row)

FrogHolder_01

https://community.smartsheet.com/discussion/comment/509078#Comment_509078

Paul, thank you so much—this is working perfectly. I have one follow up question if you have time. Is there any reason I can't simply use the number (1, 2, 3) directly in the formula instead of pulling it from an adjacent number column? I just tried it as an experiment and it seems to be working just as well. To be clear, while I agree that I'll want at least 10 Rows available for data, this won't be a column formula, so I'm fine with simply coding each row separately.

Paul Newcome

You can manually code the number into the formula, but that means updating the formula on every row. I find it is easier for me to quickly number the rows then reference the number for a couple of different reasons.

Even if it isn't a column formula, drag-filling is still a quick way to populate cells. If I enter the formula on the first and second rows and manually enter 1 and 2 on the first and second rows, I can select both cells and drag-fill them down as far as I need.

This also makes expanding much easier because if I realize I didn't give myself enough room, a quick drag-fill takes care of that much faster than having to edit each formula individually.

While it may not seem too bad for a few rows, it can very quickly get tedious. I personally wouldn't have the patience to hard-code numbers even for 10 rows simply because the number column reference goes so much faster.

FrogHolder_01

@Paul Newcome, thanks again—your thoughtful feedback on best practices is exponentially more valuable than responses to individual problems, since it makes us all better coders down the road. Thanks so much for taking this additional time.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.