Help me with a formula

Options

I need that a cell return the status of a project that i have in a summary sheet. Then right now i have this formula, but what i need to integrate in this one that is working, is that need to find by "project name", i have a column with the "Project name" in the summary and the same project name will be in another one then i need something that say IF this project name is in the column
"Project name" and pick the result of that row.

The formula right now is working but it is not looking for a specific row of an specific project.

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

Again what i am trying to do is integrate a new IF that look for an specific project name.

Thanks

Answers

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Options

    Are both project names on the same row but a different column?

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • Raul Cabrera
    Options

    Hi @Mark Poole

    Let me clarify since I might have explained it poorly yesterday.

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!