Hi all. I have a master spreadsheet with all the clients' trade
information and need to link information between the master sheet and
other separate worksheets containing the trade info for each
individual client. For example, on the master sheet, it lists every
client's quantity, price, stock, and date of each trade she made;
there is also a separate sheet for, say, client A which lists only
her quantity, price, stock, and date of each trade.

Here is what I want to do: Not only do I want to have the data in the
individual sheet updated as I update the master sheet's corresponding
data, I also want to see the new entry (of quantity, price, etc.)
automatically added to the individual sheet as I add new data on the
master sheet, or vice versa. For example, client A made a trade today,
and I record the data of the trade into the master sheet, I also want
to see the data appear on her individual sheet. I know about the
Sheet1! cell reference thing, but apparently it doesn't work if I want
to add a new entry simultaneously the individual sheet as I update the
master sheet. I heard you have to it by playing with macros and even
visual basic. What exactly should I do to make this happen?

Thank you very much!

Excel doesn't do this kind of thing very well.

If you make a typing mistake, you'll have to provide code to remove the new
entry from the wrong sheet--or update the data associated with that client.

Instead, I'd keep all the data in one sheet. Use autofilter or sort to show the
stuff I need.

But if I needed separate worksheets, I'd refresh them from scratch each time I
needed them.

You may want to look at how Ron de Bruin and Debra Dalgleish approached this
kind of thing:

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

Or:

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

Ben Yang wrote:

Hi all. I have a master spreadsheet with all the clients' trade
information and need to link information between the master sheet and
other separate worksheets containing the trade info for each
individual client. For example, on the master sheet, it lists every
client's quantity, price, stock, and date of each trade she made;
there is also a separate sheet for, say, client A which lists only
her quantity, price, stock, and date of each trade.

Here is what I want to do: Not only do I want to have the data in the
individual sheet updated as I update the master sheet's corresponding
data, I also want to see the new entry (of quantity, price, etc.)
automatically added to the individual sheet as I add new data on the
master sheet, or vice versa. For example, client A made a trade today,
and I record the data of the trade into the master sheet, I also want
to see the data appear on her individual sheet. I know about the
Sheet1! cell reference thing, but apparently it doesn't work if I want
to add a new entry simultaneously the individual sheet as I update the
master sheet. I heard you have to it by playing with macros and even
visual basic. What exactly should I do to make this happen?

Thank you very much!

--

Dave Peterson