nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Smartsheet Community

Connect with peers, share your expertise, and inspire what’s next in Smartsheet — from proven practices to practical how-to insights from fellow users and product experts.

Index Collect Function

Jessica Howell

Hi all!

I'm trying to use an index collect formula to reference 3 different name columns, and use a range from another sheet to populate their employee number.

I want the employee number to populate based on what is entered into any of these 3 employee name columns. The formula I've been trying is,

=INDEX(COLLECT({Non- Union TO- Background Range 1}, [Employee Name- DC04]@row, {Non- Union TO- Background Range 1}, [Employee name- DC31]@row, {Non- Union TO- Background Range 1}, [Employee Name- DC37]@row))

Pulling from this range,

Any suggestions?

Thanks!

Trouver d'autres publications étiquetées avec

Accepted answers

All comments

Paul Newcome

Try this:

=INDEX(COLLECT({ID Column}, {Non- Union TO- Background Range 1}, [Employee Name- DC04]@row, {Non- Union TO- Background Range 1}, [Employee name- DC31]@row, {Non- Union TO- Background Range 1}, [Employee Name- DC37]@row), 1)

Jessica Howell

@Paul Newcome thank you, but it came back with 'incorrect argument'

Paul Newcome

Can you provide a screenshot of the formula open in the sheet as if you are about to edit it?

Jessica Howell

@Paul Newcome see below,

Paul Newcome

I think I see the issue. Give this a go:

=INDEX(COLLECT({ID Column}, {Non- Union TO- Background Range 1}, IF([Employee Name- DC04]@row <> "", [Employee Name- DC04]@row, IF([Employee name- DC31]@row <> "", [Employee name- DC31]@row, [Employee Name- DC37]@row))), 1)

Jessica Howell

@Paul Newcome still not working… when you say 'id column' where are you trying to reference?

Heres my main sheet,

Here's the sheet im trying to reference. so based on the email entered, it will pair it with win#

thank you

Paul Newcome

The {ID Column} should be referencing whatever column you are wanting to pull over. I also notice the formula in your last screenshot is not the same as the formula in my last comment (see below).

Jessica Howell

@Paul Newcome yes sorry, i was using an old screenshot, i did try the formula from your last comment, but couldnt get it to work

Paul Newcome

When you say you couldn't get it to work, do you mean you were getting an error message or an unexpected output?

Do you have an updated screenshot with the most recent formula tried?

Shaz

@Jessica HowellPuisque vous suivez les heures supplémentaires ici, je crois qu'un employé travaillera dans un seul DC à la fois, donc il y aura toujours 2 autres DC vides. Si mon hypothèse est correcte, vous pouvez combiner trois DC avec la formule join puis index collect.

Essayez ceci

=IFERROR(INDEX(COLLEXT{Non- Union To- Background Range 2},{Non- Union To- Background Range 1},JOIN([Employee Name- DC04]@row:[Employee Name- DC37]@row)),1),"")

Mappez Range 2 à WIN# et Range 1 à email

Sign in to join the conversation!

Liens rapides

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.