Automatically update a couple of columns from master worksheet in other sheets

Hey Folks,

I've been searching for an answer to this for ages, and if it's been posted before, my sincerest apologies but it has eluded me so far!

In concept, the problem would seem to be minor. As you can see on the attached simplified workbook - I have three sheets "Master", "Project" and "Comms".

Essential what I want is for the "Project" and "Comms" sheets to display the same names as the "Master" sheet (Master!A) as well as a couple of extra columns that are on "Master". More importantly, I want the "Project" and "Comms" sheets to automatically update as soon as I insert a new row into the "Master" sheet (i.e. a new person). I think it's important to note that cells aren't easily referenced (e.g. Master!A2 "Brian Jones" doesn't equal Project!A2) because on the subordinate sheets I have a varying number of header rows which throws it all out of whack.

The database contains 500+ rows and I'm using Excel 2007 if that's relevant.

For clarities sake, I have already tried two solutions to the problem. Firstly, grouping the sheets doesn't work because as I mentioned above the row numbers aren't contiguous. Secondly, I've tried just referencing using =Master!A:A which works beautifully in terms of auto updating but fails miserably in that data is lost because of the varying number of header rows.

I'm sure there must be a relatively simple solution (non-macro based) surely?

Thanks for any help offered.

Hi NBVC,

Thanks for you help.

Firstly, the data in the other columns on each of the subordinate will be manually entered, all I want is for the Names to be entered once on the Master sheet and then automatically populate across the rest of the sheets.

I tried your solution, but when I then add a row (say between row 3 & 4, right-click and insert row) on the Master sheet it doesn't appear on the subordinate sheets. However it does appear when I add a row to the end of the list rather than inserting mid-way down the list.

Perhaps trying to insert a row manually as described above could be my problem?

Now we're somewhere!

The only problem is that it duplicates all of the cells from the row above in the subordinate sheet in other words it copies the data from the cells immediately above the newly inserted row rather than leaving them blank?

Cheers