vlookup/index/match to place data from one sheet into another
I compile safety data using a form that is filled out by my safety manager. In it he enters the date and if an incident occurs. If no incident occurs it uses a calculation (from my last discussion topic, still having circular reference errors) to sum the total number of days since the last incident.
My question, which has thus far stumped the smartsheet pro desk, is this...how do I place the end of week data into another sheet? I've added the "weekday", "weeknumber", and "year" helper columns. My guess is I need a cross sheet reference that is some combination of vlookup/index/match functions that checks for the end of the week. Confusion exists because the WEEKDAY function uses a Sunday as day 1 but the WEEKNUMBER function uses Monday as the first day of the week.
In the end I would like to have the circled data in separate columns on a separate sheet so that I can cleanly present week end data via a report to my boss (data currently manually entered):
Anyone know if this is possible? So far all my attempts of function combinations have come up with errors. Did a half hour pro-desk session and was unable to accomplish it. Apparently it's beyond their scope of knowledge so before I have to pay for a "consultant" (pro-desk recommendation) I thought I'd ask the community.
Answers
-
You could try something along the lines of...
=INDEX({Source Sheet Near Miss Days Since Column}, MATCH(Weeknumber@row, {Source Sheet WeekNumber Column}, 0))
-
Paul this will be a viable function as my data is sorted "newest to oldest" (it's finding the first instance of the weeknumber). It is very cool though because now it will update throughout the week for me so that's an unexpected bonus.
Any thoughts on how it would be done if the data wasn't in perfect order?
-
To use unsorted data, you would need to use a MAX/COLLECT to pull the max date for the appropriate weeknumber, then use that date to MATCH on.
=INDEX({Source Sheet Near Miss Days Since Column}, MATCH(MAX(COLLECT({Source Sheet Date Column}, {Source Sheet WeekNumber Column}, Weeknumber@row)), {Source Sheet Date Column}, 0))
-
I'll have to try this on one of my other sheets as well.
Thanks for the help Paul!
-
Happy to help! 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!