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:

IF [Country] is listed as United States,
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?

Find more posts tagged with

Formulas

Sheet Summary

Accepted answers

All comments

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.

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!

Best,

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!

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?

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.