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

jromano

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!

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.

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.