INDEX COLLECT FORMULA

F. Martin

Hello everyone,

I'm trying to do a workaround to avoid a circular reference that is happening when doing cross sheet formulas. For that purpose I'm building up a formula that brings to a column the end date of the last activity of the project

Activity Start Date End Date Activity 3 Date

-Project 1

Activity 1 01/01/22 05/02/22 26/06/22

Activity 2 06/02/22 25/03/22 26/06/22

Activity 3 25/03/22 26/06/22 26/06/22

-Project 2 Start Date End Date Activity 3 Date

Activity 1 01/07/22 25/09/22 09/12/22

Activity 2 26/09/22 25/11/22 09/12/22

Activity 3 26/11/22 09/12/22 09/12/22

How should I look up that Date?

KC Jones

I might be able to help but I'm not sure.

What formula(s) have you tried? I think an INDEX formula might work, but may need different functions instead of/in addition to COLLECT, but I would need a bit more detail. Are you trying to put the formula in your project sheet or the other sheet? And is that a sheet, report, etc.? Also do you need it to return that value once, or, if it updates, do you want that to be updated as well?