vlookup/index/match to place data from one sheet into another

Chris Rea

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.

Paul Newcome

You could try something along the lines of...

=INDEX({Source Sheet Near Miss Days Since Column}, MATCH(Weeknumber@row, {Source Sheet WeekNumber Column}, 0))

Chris Rea

https://community.smartsheet.com/discussion/comment/253464#Comment_253464

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?

Paul Newcome

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))

Chris Rea

https://community.smartsheet.com/discussion/comment/253484#Comment_253484

I'll have to try this on one of my other sheets as well.

Thanks for the help Paul!

Paul Newcome

Happy to help! 👍️