replicate columns across multiple worksheets

[sarah]

A user has a precreated workbook with 12 sheets named jan - dec. Each
worksheet has the names of people in one column and their total holiday
allocation in another column.

When they enter a new name at the end of the list, the name gets replicated
across all worksheets.

However, when they add a new row in the middle of the current list of names
and add a name to the column, this doesn't get replicated across all other
worksheets.

There is obviously some kind of formatting, but I don't know where to look.
Please could someone advise.

 

[ryguy7272]

Sounds like Event Code -- right-click on the tab and select View Code.
http://www.cpearson.com/excel/AppEvent.aspx

http://exceltips.vitalnews.com/Pages/T0586_Worksheet_Events.html



Regards,
Ryan---

 

[MrAcquire]

While you could solve the user's problem with code, I suggest redoing the
workbook with a better design, have one worksheet (or one worksheet per year
if you want to save history) with headings as follows. That is, combine the
12 worksheets into one.

Name Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec

 

[Gord Dibben]

The data could be replicated in the other worksheets via linked cells formulas
like

=IF(Sheet1!A1="","",Sheet1!A1) which is then dragged down and across on each
sheet. Most likely far enough down and across to cover new names at the end of
the list on Sheet1

Inserting a row in the middle in Sheet1 will not carry the links over to the
subsequent sheets, hence no replication.


Gord Dibben MS Excel MVP