Synchronizing lists or views between worksheets

[ascorbique]

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

 

[ascorbique]

I forgot to say I'm using Excel 2003. I would be interested by a
solution on Excel 2007 if it is the only one, but I doubt my company
will upgrade to Office 2007 really soon.

Thanks

John

 

[Dave Peterson]

I don't think excel does this kind of stuff very well.

But you could just use one worksheet (add some columns for that other info).
Then use data|filter|autofilter to see what you want.

You'll find that a lot less can go wrong when there's only one worksheet getting
updated.

And if you really need multiple sheets, you can create "report" sheets (no input
allowed) that can be refreshed from the main sheet on demand.

If you want to try...

Ron de Bruin's EasyFilter addin:
http://www.rondebruin.nl/easyfilter.htm

Code from Debra Dalgleish's site:
http://www.contextures.com/excelfiles.html

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- replaces old data with current.
AdvFilterCity.xls 55 kb

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)