Multiple index(collect... criteria formula

I am a Smartsheet beginner and hit a bind as I believe what I am seeking is fairly complicated, but I believe the program can do it. I have three sheets that have the same set up for reporting across three different projects. I want a MASTER file that will auto populate from these three sheets based off the name selected.

FIRST. So far, the index(collect... formula does exactly what I needed but just that for one refence set. If I was to add the same line of code (reference set) for a different sheet, within the same collect formula, there is an error:

For example, in one column I want the job code displayed that matches the name at row index(collect{sheet1jobcode}, {sheet1name}, name@row), {sheet2jobcode}, {sheet2name}, name@row), {sheet3jobcode} {sheet3name}, name@row}), 1).

So what I am seeking is, between these three sheets, whichever name that matches the selected jobcode at a row, to populate said cell.

Now...

SECOND. That formula set brings up another issue which is multiple references that span days.

When I used one refence set of index(collect... it worked, but when I selected the same name in a different cell location under a different day, the same exact data was posted as if there needed to be additional coding to differentiate the information in different cells.

I'm not trying to have set spaces from three different sheets because that's the same as cell linking.

Attached is snapshot of the format of all three reference sheets and the MASTER sheet.

This new formula would populate the Hours, job number, cost code, and description fields respectfully.

Screenshot 2023-11-01 142012.png

Find more posts tagged with

Accepted answers

All comments

Hollie205

Index Collect can only bring in one value that fits the criteria. If you need to search for the same value on different days you would need to add a criteria for the day to differentiate which value you are trying to bring in.

I would also recommend adding if errors to your formula so it will not bring an error if nothing is found matching and add Index collects together to get it to bring in from your other sheets.

iferror(index(collect{sheet1jobcode}, {sheet1name}, name@row),1),"")+iferror(Index(collect( {sheet2jobcode}, {sheet2name}, name@row),1),"")+iferror(index(collect( {sheet3jobcode}, {sheet3name}, name@row}), 1),"").

Veritas

Hollie,

Thank you for responding! So, the first step to this long formula has been achieved, this formula now works across all three sheets! I will try to look into how to differentiate by day. If you have any idea of how to start that formula code, that would be great. I think I may have an idea...will respond if it works or not, need to test.

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.