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.



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!