How to update one sheet from multiple sheets by inserting new rows for each personnel.

Hi,

First things first. Why are you mandating users enter data on their own individual sheets?

You're building a database so you may as well do this properly and capture the data on a single sheet. Use essentially the same columns of data you probably have now but without any formatting or merged cells plus an extra column to record the personnel id of the person who adds a record.

I usually keep a dedicated data entry row of cells above the data which the user completes, then I have them click a button which adds a new first row to the data, copies the data entry row and pastes it to the new row.

You are making the same mistake that I see time and time again. You are mixing up the two elements of data capture and final reporting. The two require quite different treatments.

A lot of people start by designing the form / sheets that they expect to see as the final report, and then wonder why it's so difficult to subsequently analyse and summarise or extract information from it.
You should always capture data in a simple two dimensional table and worry about reporting information from it afterwards. Without exception doing this you will always be able to easily obtain management information. Rarely is this the case if you start the other way round.
You will also throw open the whole wonderful world of the powerful Pivot table functionality.

So, and with respect you don't NEED to originate data in individual sheets. I'm assuming you are copying and pasting from the sheets they send so just stack them underneath each other on the single summary sheet using that as a database and add an extra column that records their ID. Then you may use Excel's standard data filtering functionality or a Pivot Table to report whatever it is you wish to report.

Hi,

Can't you just link directly from your master workbook to each of the personal workbook files. e.g.

Formula:

Please Login or Register  to view this content.