Automatically copy data from one worksheet to others

[AlisonB]

Hi,

We use Excel 2003 with Win XP Pro and I hope you can help me.

I have a workbook with three worksheets - one for each team I
support. Each team only use their own worksheet. In the same place at
the bottom of each worksheet is a list of names (Col A) and dates (Col
B). Is there a way to make this list show the same information on all
three worksheets?

What I would like to happen is that if one person adds a name and date
on their team sheet, the information is automatically copied to the
next empty row on the other two worksheets. This way each team can
each see the complete list.

All suggestions welcome.

Kind regards,

Alison

 

[Don Guillett]

Forgive me but why not just have ONE worksheet for all the info?

 

[AlisonB]

Hi,

No problem - simply because each team doesn't want to wade through the
other's data when working on their own data. The data on each sheet is
only relevant to that particular team. The list I am trying to make
appear on each worksheet is the only set of data they all need to see.

Kind regards,

Alison

 

[Don Guillett]

Try one with data>filer>autofilter>

 

[AlisonB]

Hi,

Thanks, but each team wants their own sheet. They are not experienced
Excel users and would freak out at being asked to use something like a
filter - it is hard enough to get them to use Excel at all. The sheets
are set up for them and everything has to be as quick and simple as
possible.

Do you (or anyone else) have any other suggestions?

Kind regards,

Alison

 

[Puppet_Sock]

I have a workbook with three worksheets - one for each team I
support. Each team only use their own worksheet. In the same place at
the bottom of each worksheet is a list of names (Col A) and dates (Col
B). Is there a way to make this list show the same information on all
three worksheets?

What I would like to happen is that if one person adds a name and date
on their team sheet, the information is automatically copied to the
next empty row on the other two worksheets. This way each team can
each see the complete list.

If you can decide who is "owner" of any given chunk of data, then you
have one scheme. If not then another scheme.

If you can decide on the owner, then only that person (or group)
will change it. The data will be changed only on the sheet that
the owner uses, and everywhere else it will be a formula pointing
at the place it is changed.

Example: On the sheet of the owner, cell B14 has the text
John Smith in it. Say that sheet is called Group1Sheet.

Then on the sheet used by group 2, instead of the text
you have a formula. Cell H37, the data location on that
sheet, will have a formula like so.

Group1Sheet!B14

And so on, filling in all the data that is visible for all sheets.
Then make the part of the sheet that is visible to everybody
protected so it can't be changed directly. Only allow the
parts the groups are to provide to be changed by them.

If you can't decide on the owner of a given chunk of data,
that is it will be changed by two or more groups, then you
may need to handle change events. That would require you
to learn some VBA. Whenever anybody typed anything in
a cell that other groups should see, the VBA would update
it across the several sheets. This is more complicated from
a coding standpoint. Search back through this group for
help on handling change events. It gets discussed here
fairly often. For example, there is another thread here this
morning already talking about it.

Possibly you can have a combination. If some data is to be
changed by several groups, it could be located on a special
"in common" sheet that everybody is to use. Then all sheets
would have formulas that pointed at this common sheet,
and those chunks of data would only be changed on the
common sheet.
Socks

 

[Don Guillett]

I just re-read your original post and I think you said that you wanted three
IDENTICAL sheets.... If that is, indeed, true then why 3???

 

[AlisonB]

Hi,

Thank you for all your help. I will check out handling change events
next.

Don, I am not sure where any confusion has arisen but I have not said I
want three identical worksheets. The three worksheets contain
different information - but they all have a list that starts at the
same point on each worksheet. What I am trying to achieve is that if
one team adds a name and date to their list, it is then copied to the
lists on the other two worksheets so that each team sees all the names
and dates added to any list. I hope this clarifies things.

Kind regards,

Alison

 

[Puppet_Sock]

Thank you for all your help. I will check out handling change events
next.

Actually, I'd think very carefuly before going that route.

It sounds like you have three groups of users who all want to
own the same data. This could easily turn into a tug-of-war
in which you wind up getting blamed.

Consider: Data item A on user group 1's sheet. Some user
sets it to "John Smith" and is happy. It gets updated on
everybody's sheets, and we move on.

Then on group 2's sheet, a group 2 user says "Hmmm...
That's supposed to be Smith, John." And he changes it.
And it gets updated on everybody's sheet, and he's happy.
And we move on.

Then group 3 wants it to be "Smith, Mr. J." and *they*
change it, it updates everybody, and moves on.

And then the group 1 user comes back and gets irate.
And the cycle repeats. And they wind eachother up
until they come looking for you with sharp things.

It sounds like you might want to sit down with some
reps from your user groups and decide: Who owns
any given chunk of data? Who gets to decide on content?
Who is the authority? If there's a conflict over content
(or format or order or whatever) what rules will be used
to decide?

Maybe what you really want is just three different views
on the same basic data set. There are many ways to
get that. You select which one by determining who gets
to change a given chunk of data. Give each user group
a read-only view on all the data, formatted the way they
like. And give each group a changes-allowed view on
the data they own.

If *everybody* can change data, then you may have to live
with such tug-of-wars as I just described. You would then
have to just have an official policy such as "don't go
changing things because some minor formatting is
not the way you want it. If the information you need is
there, leave it alone." Or whatever works and stops
users from smacking eachother.
Socks

 

[MartinW]

Hi Alison,

You could put your list in sheet 4 then have sheets 1,2 & 3 referencing the
master list. Have the cells in your working sheets locked and a note saying
"To add new names go to sheet 4"

Just a thought
Martin

 

[MartinW]

Hi Alison,

You could put your list in sheet 4 then have sheets 1,2 & 3 referencing the
master list. Have the cells in your working sheets locked and a note saying
"To add new names go to sheet 4"

Just a thought
Martin