Synchronizing lists or views between worksheets

Giganews Newsgroups
Subject: Synchronizing lists or views between worksheets
Posted by:  ascorbiq…@gmail.com
Date: Wed, 2 Jul 2008

Hi everyone,

a) What I need help on

I'm currently writing a long list of features for a product. Each
feature has a very large number of fields which need to be filled in
by different teams. I'd like to have a unique Excel document
containing all this information but with a way to show only the
relevant columns each time one of the team fills in the document.

Example: the columns for my dataset could be
id, name of feature, comment from business, priority for business,
comment from technical team, priority for technical team (but actually
there are many more)

b) What I tried and did not work

What I first tried is having several worksheets. The main worksheet
will contain the columns every team should see (ie: id and name).
There would be an additional worksheet per team, linking to these
common columns and then having the special columns for this particular
team.

Example:
- Main worksheet columns:
A: id (filled in by me)
B: name of feature (filled in by me)

- Business worksheet:
A: filled in by formula "= 'MainSheet'!A2" (and drag and drop)
B: filled in by formula "= 'MainSheet'!A2" (and drag and drop)
C: comment from business team (to be filled in by business team)
D: priority for business team (to be filled in by business team)

- Technical worksheet:
A: filled in by formula "= 'MainSheet'!A2" (and drag and drop)
B: filled in by formula "= 'MainSheet'!A2" (and drag and drop)
C: comment from technical team (to be filled in by technical team)
D: priority for business team (to be filled in by technical team)

If I modify the id or the name of a feature in the main worksheet, it
successfully updates in the other worksheets. What I am missing is if
I insert a new feature in the main worksheet it is not inserted in the
other worksheets (which may be logical but is not the behavior I'd
like to have). It behaves the same way whether I insert the new
feature in the middle or at the end of the list.

Can you help me with than kind of list synchronization between
worksheets?

The other solutions I've thought to would be no to duplicate the data
using the "= 'MainSheet'!A2" formula (or maybe only the id) and to
create some kind of view using the id. I just can't find any
information about that either.

Any help would be greatly appreciated. Feel free to ask for more
details if I've been unclear.

Thanks!

John

Replies