This is a very complicated problem to solve that needs a bunch of setup work to get you to the point where you can create your formula.
I can show you a demo of how we've set this up for other clients (we are a Quick Base development company) if you like. Visit my profile page or
www.cirrusops.comBasically the problem is this:
Tasks are not aware of any other tasks. Even with predecessors, they only become aware of the task that falls before it; though not any real information about the task, just the record id# of it, which is not helpful in your formula.
Now, using summary fields in the relationship between projects and tasks, you can summarize info about specific tasks up to the project and then back down to all tasks with lookup fields. If you have a set list of tasks everytime, this can be done fairly easily. You can summarize whether a certain task is complete or not, even when it was completed. The other tasks related to that project can utilize that information to determine where it specifically fits into the project plan and if it is next or not. If you do not have set tasks lists, this method falls apart. In that case, we use some coding to solve the problem.
Hope that is helpful - reach out to continue the conversation.