Formula IFS from other source sheet


Hi good day. I have this formula right now that is returning one value, but it still is missing some part. I need

=IFERROR(INDEX({Weekly Status - Overall Status}, MATCH(MAX(COLLECT({Weekly Status - Date}, {Weekly Status - Complete}, 1)), {Weekly Status - Date}, 0)), "N/A")

I have an "input sheet" where all the projects are listed by row. Each project has multiple columns, including "Project Name" and "Overall Status" (R, Y, G). This is where the PMs will input the weekly status summary for each project.

Everything is good so far.

Secondly, I created a Metadata sheet for each project. Here, I need a cell called "Overall Status" to return the value for a specific project "X". Essentially, I want to be able to pull the "Overall Status" or any other related information for a specific project from the input sheet. In the formula, I need to reference the project I am looking for in the "input sheet

Below some screenshots

Input sheet

Metadata sheet. Right now, is returning the first value of the project with Date 5/27 and that is checked in the Input sheet


Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!