Summing across multiple sheets

[bqheng]

Hi,

I have this workbook that tracks leave and time off. In Column D of the Time off Balance tab, you will see a formula that I used to calculate the balance of time off each staff has. This requires the specific row the name of the staff is in to be entered in the formula. Problem arises when I slot in new staff in between current staff and the row number changes. Im think of using SUMPRODUCT, but do not know how to input the correct formula. Can anyone help me with this please?

Attached is the file

[mehmetcik]

Why not use a Macro to insert a new row into every sheet and copy your new name and your formulas to all sheets?

[bqheng]

Hi mehmetcik, thank you for helping. How do you propose to do that? I have reuploaded the same file without protection

[mehmetcik]

This would normally do the trick

but for some reason it doesn't work on your workbook.

I can select a single row manually

but not with a macro. I have no explanation. Perhaps you do.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[bqheng]

Hi, the codes were written by someone else, so I do not have any explanation either. How about we not use macro and use formulas instead? will that be easier?