Creating a dynamic summary sheet

[FenelonPaul]

I have a workbook with 4 sheets to keep track of membership in an organization. Each sheet represents a different membership category, and all sheets contain about 20 columns of data on each member.

I need to create a 5th sheet that has only the members' names and a third column with their current status. This fifth sheet should contain all members from all of the 4 sheets. It also needs to be sorted by name, and a 4th column added to be a sequential number, starting at 1, for each member.

And finally, all this needs to be dynamic - so that if a new member is added to any of the 4 sheets, or any status changes are made to the 4 sheets, this 5th sheet will be automatically updated, sorted, and the sequence numbers regenerated.

Is this at all possible? (prefer a solution without VB which I am not familiar with )

[macropod]

Is there any reason the data can't all go on a single sheet? I imagine you're collection almost the same data for each membership category and all you'd need is a single column to differentiate the membership category. Extracting summaries, etc. from a single sheet is much easier than trying to do the same across multiple sheets, especially when you start talking about sorting. With a single sheet, filtering by status would be a trivial exercise by comparison to compiling a list from 4 different sheets.

[FenelonPaul]

Thanks for the reply.

Yes, it could go all on one sheet.. the person maintaining the records is not that fluent with Excel, and I am trying to make it as easy as possible for her, by separating the different types. It also makes it easier for her to print reports, as each is on a separate sheet. We just need a master list now, with only the names. I can probably live without the sequence numbers and the sorting.. just would like a sheet that would update whenever a name is added or removed.

[macropod]

Yes, but by using multiple sheets, you're actually making it harder. FWIW I recently constructed some workbooks for use by novices for generating monthly rosters and summarising data across a number of activities. All the inputs are done on one sheet and all the reports etc. are generated on other sheets via formulae. This has eliminated both the need to have a separate sheet for each month's roster and the need to transcribe the data from there to the periodic summaries.

In your case, depending on the types of reports needed, a similar outcome could be achieved. Stick with the multiple worksheets, though, and there is no way any of this can be done without macros. And, once you get into macros, you're into the demands of a much higher skill set to develop, use and maintain them. Development would not be possible without access to the workbooks. Mind you, I'm not saying everything you want can be achieved without macros but that your current approach mandates them.

[Pecoflyer]

I couldn't agree more

[FenelonPaul]

I am going to explore another method of doing this.. thanks!