How to link worksheets to accommodate new rows dynamically

[helenc]

Hi,
I have 5 worksheets which I need to summarise into one sheet. They are text values (not numerical). I am able to link them so they summarise but can only get the summary sheet to update cells that already exist, if changed, I cannot seem to find an option to dynamically update the summary sheet with a new row if I insert one to one of the other worksheets. Is this possible? I have no VB skills!

I've looked at the consolidation functionality but that only works if its data I want to 'do something with' i.e. sum, not just copy over. I want to consolidate the textual information - first 8 column headings on each worksheet are consistent.

I am sure the answer is out there on the boards but have searched high and low. Any help gratefully received.

Excel 2007/Vista user!

Thanks.

 

[GlennUK]

Explain what type of summary you are creating.

 

[helenc]

The summary is a compiled stakeholder list merging content from different organisational perspectives - names, countries, roles etc (I know excel is probably not the right tool - but the only one I've got!)

Each worksheet has a different purpose in itself along with additional narrative that doesn't need summarising (only 8 columns are consistent across all). I need to use the summary sheet to sort the various stakeholders by country. Each worksheet varies from 100-200 lines.

Hope that is making more sense.
Thanks.

 

[GlennUK]

Would this help:

http://blog.contextures.com/archives/2009/08/24/create-a-pivot-table-from-multiple-sheets/

?

 

[helenc]

Thank you for the steer. I am afraid the content was beyond my excel skills. I think I'm going to settle with a simple linking which will accommodate at least changes to existing lines and a manual add for new lines.

Maybe one day I'll investigate complex pivot tables!

Many thanks.
helenc