Need help with sheet reference formula to grab assigned user for a specific day.

I am working on some cross sheet data and sharing and would like to list in a cell of a sheet the user [Technician] assigned to a space [Bldg-Room] on Thursday from another sheet. There is a column for each day of the week on the sheet being referenced. [Thursday]

I can use this formula within the current sheet but I seem to run into issues trying to apply a similar method to a sheet reference formula.

=INDEX(Technician:Technician, MATCH([Bldg-Room]@row , [Bldg-Room]:[Bldg-Room], 1))

Find more posts tagged with

Formulas

References

Accepted answers

All comments

gwilkinson

I was able to get something working but it does not seem to be grabbing the assigned contact for the given day.

=INDEX({Technician 1}, MATCH([Bldg-Room]@row , {Bldg-Room 1}, 0))

The AI Generator in Smartsheets keeps suggesting something like this.

=INDEX({Bldg-Room 1}, MATCH(Technician@row + Thursday@row , {Technician 1} + {Thursday 1}, 0))

I seem to be going in circles. :(

Andrée Starå

https://community.smartsheet.com/discussion/141483/need-help-with-sheet-reference-formula-to-grab-assigned-user-for-a-specific-day

Hi,

I hope you're well and safe!

You need to use an INDEX/COLLECT combination or combine the values, similar to your second option, or combine the values in a so-called helper column in both sheets.

Make sense?

https://help.smartsheet.com/function/index

https://help.smartsheet.com/function/collect

Did that work/help?

I hope that helps!

Be safe, and have a fantastic day!

Best,

Andrée Starå | Smartsheet Expert Consultant & Partner / CEO @ WORK BOLD

✅ Did I help answer your question/solve the problem? Please support with💡 ⬆️ ❤️, and/or ✅ Answer. This will make it easier for others to find a solution or help answer. I appreciate it, thank you! 🙏

gwilkinson

https://community.smartsheet.com/discussion/comment/491385#Comment_491385

I am just savvy enough to be a danger to myself with formulas still. I could use a bit of an example for that. I've never used a collect formula and am fairly comfortable with indexes…

gwilkinson

I think I may have found the right string… But I am wondering, can I add more than one criterion to the section referencing {Thursday 1}? I would like to have it list the name of the {Technician} regardless of the assignment in the {Thursday 1} Drop-Down Menu column. With what I have I am just able to grab the name of technicians doing "health checks" in said {Bldg-Room 1} space.

=INDEX(COLLECT({Technician 1}, {Bldg-Room 1}, [Bldg-Room]@row , {Thursday 1}, "Health Check"), 1)

Andrée Starå

https://community.smartsheet.com/discussion/comment/491436#Comment_491436

Excellent!

Do you mean that you'd like to collect all, regardless of whether it's a Health Check?

✅ Remember! Did I help answer your questions or solve the problem? Please support with💡 ⬆️ ❤️, and/or ✅ Answer. It will make it easier for others to find a solution or help answer. I appreciate it, thank you! 🙏

gwilkinson

I would like to collect all but I was not able to figure it out. But I was able to get it working by specifying exactly what qualified the cell. I am open to more suggestions to simplify or make the search more broad…

=INDEX(COLLECT({Technician 1}, {Bldg-Room 1}, [Bldg-Room]@row , {Thursday 1}, OR(@cell = "Health Check", @cell = "Cage Change", @cell = "USDA Tasks", @cell = "Room Check", @cell = "Weekly Sweep/Mop", @cell = "Monthly Sweep/Mop", @cell = "Stock Supplies", @cell = "Move to conventional fridge", @cell = "Move to carcass bin")), 1)

Andrée Starå

https://community.smartsheet.com/discussion/comment/491485#Comment_491485

Excellent!

One way to potentially simplify it, if you want to include any option that isn't empty, could be to use <>"" instead of adding all the options.

Make sense?

✅ Remember! Did I help answer your questions or solve the problem? Please support with💡 ⬆️ ❤️, and/or ✅ Answer. It will make it easier for others to find a solution or help answer. I appreciate it, thank you! 🙏

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.