Formula to populate a cell based on another cell's formula criteria

Hi Again,

I have a formula in row 1 of column Project Status that identifies the last value in the column that has any status except Not Started:

=INDEX(COLLECT([Project Status]:[Project Status], [Project Status]:[Project Status], <>"Not Started"), COUNTIF([Project Status]:[Project Status], <>"Not Started"))

In the example below, it identifies row 9 as having that value (On Target) and places it in row 1 of the Project Status column.

I populated the Trend, Next Steps and Impediments columns with values and want a formula that takes the values from the same row 9 and puts them into row 1.

I tried the following formula but the cells remain blank instead of emulating the values in row 9.

=INDEX([Trend]:[Trend], MATCH([Project Status]1, [Project Status]:[Project Status], 0))

I think I need to have the formula above refer to the logic of the first formula but I'm unsure of the syntax.

Please advise!

Find more posts tagged with

Formulas

Accepted answers

Kelly Moore

Hey @jromano

Try this

=INDEX(COLLECT(Trend:Trend, [Project Status]:[Project Status], [Project Status]1), COUNTIFS([Project Status]:[Project Status], [Project Status]1))

Will this work for you?
Kelly

jromano

Wow! This is perfect! Thank you!

Interestingly I had to tweak it for the Next Steps and Impediments column to put [ ] around Next Steps and Impediments for it to work.

All comments

Kelly Moore

Hey @jromano

Try this

=INDEX(COLLECT(Trend:Trend, [Project Status]:[Project Status], [Project Status]1), COUNTIFS([Project Status]:[Project Status], [Project Status]1))

Will this work for you?
Kelly

jromano

Wow! This is perfect! Thank you!

Interestingly I had to tweak it for the Next Steps and Impediments column to put [ ] around Next Steps and Impediments for it to work.

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.