Duplicate tasks in a project tracker - formula and data consistency help

Hey Team!

Hope you are keeping well.

I am hoping you can help me a formula in a Project Tracker i have created.

I have 4 platforms - A to D and each platform has tasks assigned to it which need to be completed in order based on the sequence in column A.

Some of the tasks are duplicated across 3 or 4 of the platforms. So for example:

Task 1 is duplicated across platforms A, B and D. I want to be able to update the Task 1 status to "completed" in platform A and for this to then to feed through to the task 1 in platforms B & D.

At present, i have just linked all of the duplicated tasks to the relevant platforms, however in my real world project tracker, i will have c5,000 lines of tasks to track and need way of ensuring that all duplicates feed through from their source to the other platforms and also i need a check to ensure that the data across duplicates is correct and consistent.

In terms of formula consistency checks as i'm going to working with such large amounts of data, i was just going to sort all of the data by duplicate in column D and 'eyeball the data to ensure that it looked consistent but when to this check, my linked formulas change cells, therefore i need a formula that's durable enough to allow it to be manipulated to be 'sorted' in a different sequence to that in column A. Once i have done my consistency checks, i will once again for the data back in sequence (column A).

I've enclosed a spreadsheet of what i'm trying to achieve with Column F showing the desired outcome. Columns G and H are further comments i've added to detail where data comes from.


Thanks for your help

Please try pasting the following into cell F4 and copying down:

Formula:

Please Login or Register  to view this content.

Note that you could sort by either column C or D.
Let us know if you have any questions.