Update reference cell after inserting new rows

Hello everyone,

I am trying to build up a reference function in Excel with VBA. The idea is to link information from one row and used for another (see below example), however when a new row(s) inserted in the table, the reference links are no longer matched. I have no idea whether I should use Dynamic function, indirect , Refersto.... or other functions. Please HELP!!!! Thank you.

Example
(ex. row 1 consists Task: A / / Start: Jan / End: 2 months
row 2 consists Task: B / Predecessor = 1 / Start: =C1 + D1 / End: 2 months

The start of month of task B is taking the information from A by indicating row no. 1
Question is how to get row no. update if a new row inserted above task A. ie. if 2 new rows inserted, the cell at Task B should be 3 instead of 1.

Here is my VBA code.

Please Login or Register  to view this content.