using reference with inserting rows

[excelmanager]

My case is pretty simple. Let me explain that I really would like to know if there is a solution for this without VB.

I have a (see attached file) workbook with 2 sheets.
sheet 1: planning
sheet 2: actuals

In the planning the tasks are defined, with in the columns the planning hours.
in Sheet 2, there is a reference made to the defined tasks, where the actuals should be written.
Now, during the project, I want to insert a task between task 2 and task 3.
This messes up my references AND my actuals.
Is there any solution for this, or a smarter (preferably not in 1 sheet) to do this?

thanks.

[Azam Ali]

it needs further explanation

what are your required results?

[excelmanager]

hi azam,

I've updated the example.
What I want, is to track the actuals hours on a task versus the original planned hours.
So, there is one sheet where the planned hours (and tasks) are, and a sheet where the actuals are. The actuals are using a reference to the tasks defined in the planned sheet.

Now, during the process, a new task is added in the planned sheet (see attached example, the RED task in sheet 'planned'.
What I would want, is that the sheet 'actuals' also inserts a row at the same place (using reference?) but with the actual-values still on the right position.

[Azam Ali]

hi excelmanager

you should insert the task by selecting both the seets (i.e. group) instaed of referencing the actual sheet with standard. This will insert the task in both worksheet at same row.

the purpose of both the sheets in same sequance may be to prepare a comparison, if it so you should use the index+match functions to prepare the comarison. this could eliminate your need to keep both workbook in the same sequance

[excelmanager]

Azam,

although I think I am a 'pretty good' excel user, this function I did not know. And, solves the problem. Thank you for that.

small note: the remark about index-match I do not understand how to use this, but the grouping solves my initial question.

[Azam Ali]

see summary sheet in attatchment

[excelmanager]

Azam,
thank you for clarifying this.
I myself am using 'sumif' over time, but this works great too!