IF formula to pull biweekly updates


I'm trying to create a form that I send out to partners on a biweekly basis. I'm working with partners from 5 different countries, and I want their updates to be pulled using the sheet summary into a dashboard.

Is there a way to do an IF formula with an INDEX formula to get the following results:

  1. IF [Country] is listed as United States,
  2. THEN index [Updates] column from the most recently added line

I'm worried if I use the row index it won't work because our partners in the US may submit updates before our partners in Mexico for one week, but then the next time partners in Mexico may submit updates before partners in the US.

Any suggestions?


  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    Hi @Sophie Knudson 

    Hope you are fine, Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.

    PMP Certified


    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @Sophie Knudson

    I hope you're well and safe!

    Have you looked into using Update Request(s) instead? You could use it in combination with a so-called Archive sheet to record the historical data.

    Would that work/help?

    I hope that helps!

    Be safe and have a fantastic week!


    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!


    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Sophie Knudson
    Sophie Knudson ✭✭✭✭

    Thanks both,

    Unfortunately I don't think an archive sheet would help in this case. The main issue I'm running into is in using the sheet summary and index formula together, where I need to indicate a row to pull from. However, the countries that are providing updates won't be in a sequential order every time.

    One week it may be our US row is updated in row 1. But the following two weeks it may be in row 3 because Mexico and Canada submitted their form after the US colleagues did.

    Any suggestions for getting around this?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!