How to Update a exactly sheet tab row with a value from a imported data in another tab in Google Sheets?

Question

I have this tab in Google spreadsheet [https://docs.google.com/spreadsheets/d/14ZBff8R6brLYWtfv70V-tyG0HE-sX_ejySvplQqZftM/edit#gid=1450402909]

Sheet All Tasks, Candice Tasks, Others People Tasks

At Sheet All Tasks I have a column to add the status from my projects.

Columns date, task, Comment, status

each row is a different task and assigned to a different person.

The column Status Has (Complete, In Progress).

I'm using this to import only my tasks to Candice Tasks =QUERY( ImportRange( "Key", "All Tasks!A3:I1000" ) , "where Col3 = 'Candice Tasks' order by Col2 desc")

But I need some way to update the status in Candice Tasks and automatically updates the cell in the column status at my Sheet All Tasks in the same task row I altered at my sheet.

Any Help Please?

Answer 1

paste in 'All Tasks'!A3 cell:

=ARRAYFORMULA({
 INDIRECT("Candice!A4:M"&COUNTA(Candice!B4:B)+3); 
 INDIRECT("Dhruv!A4:M"&COUNTA(Dhruv!B4:B)+3)})