merging worksheets

[Deborah J Doyle]

I have set up spreadsheet that is now rather cumbersome, and I am
looking for ways to make it less so.

I have a large list of clients and about a dozen or so payors to these
clients all in one worksheet. I am wanting to reorganize in such a way
that I can have a worksheet set up for each payor, and list payments to
clients in the appropriate worksheet, determined by who the payor is.

My question is: Is there a way to then have a summary worksheet that
would list all the clients, linked to the payor worksheets so that it
would update as new data is entered, and could I sort them by client,
alphabetically?

Thank you for your help.

 

[Dave Peterson]

How about an alternative?

Keep all your data on one worksheet. But apply Data|filter|autofilter to that
range.

Then use the dropdown arrows to just view the data that you want.

===

If you absolutely have to have separate worksheets, I'd still put all my data on
one sheet, then refresh those other sheets whenever I needed to. I wouldn't try
to make the data automatically go to each sheet as soon as it's entered.

You could have a macro do the work for you.

You may want to try Ron de Bruin's Easyfilter:
http://www.rondebruin.nl/easyfilter.htm

Or look at the way Debra Dalgleish does it:

Debra'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

or

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

 

[Bill Ridgeway]

There are benefits to keeping all related data in worksheets within a single
spreadsheet.

It saves having to open several spreadsheets
It makes sure that any references to cells will change as the
spreadsheet changes.

Regards.

Bill Ridgeway
Computer Solutions

"Deborah J Doyle"

 

[Deborah J Doyle]

Thank you!!
I am new at this forum. Would you mind telling me how I would find
"update sheets from master"? It sounds like a great solution to what
we are trying to do!

I would also love to learn about macros. any suggestions on a good
beginning approach?
Thanks again!

 

[Ron de Bruin]

Hi Deborah

You can find it on debra's site
http://www.contextures.com/excelfiles.html#Filter

I have code also on my site
http://www.rondebruin.nl/copy5.htm

 

[Ron de Bruin]

See also
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Jerome Ranch]

The probelm with the same woorkbook approach is that multiple users
can't have the file open for editing simultaneously.
I have the same issue to solve - how to merge (consolidate) sheets in
different workbooks that have the same columns but different numbers
of rows. I think the best solution is Access, but I'm always onthe
hunt for alternatives


Jerry