Reference / Copy Dynamic Data

[mupps]

Have a worksheet listing products and prices for numerous suppliers.
eg.:

Supplier--Product---Price
ABC-------apple-----1.00
ABC-------orange----1.20
XYZ-------brick-----3.40
XYZ-------cement----0.80

This worksheet will change often.

What I would like is to reference this information on other worksheets.
I would also split the info onto a worksheet for each supplier.
Therefore i will have a worksheet for ABC and for XYZ, and the info in
these worksheets will change as the main "index" worksheet changes.

eg.
Worksheet ABC contains:

Product---Price
apple-----1.00
orange----1.20

Worksheet XYZ contains:
Product---Price
brick-----3.40
cement----0.80

have looked at formulas, but this seems messy. Is there a VBA method
that could automate this task?

Any thoughts/suggestions appreciated.

 

[Jim Sweet]

You may not need a VBA solution.

First I would name the data range in your source data worksheet. Include a
goodly number of blank rows in the range so that new data will be included in
it as well (I take it that the "index" data updates frequently..

Then I would create an MS query selecting for each supplier from this range
and embed a query on another worksheet. You could then refresh the data for
each supplier manually by right clicking on the corresponding query table and
choosing Refresh Data. This reruns the query and should return any new or
changed records from your source table. Do this for each supplier and you
have pretty much what you're describing here. Depending on how often your
"index" sheet changes you may want to update the supplier sheets at
intervals. To automate this process, right click on each of the query tables
and select Data Range Properties. This allows you to set a number of
properties for the query table, among them you'll find a "Refresh every.."
option. Check the box next to it and you set a timed refresh interval for the
query table. This can be set to fire as often as every minute. When done,
each supplier query will requery as often as you want and display
automatically any updates.

If it bogs down, it may be due to a large number of suppliers/queries and
you'll need to increase the refresh interval. With just a few suppliers, it
shouldn't be much of a problem.

This could also be done with VBA at even shorter intervals.

No doubt there are other ways, but this won't take any code and if the
refresh rate is fast enough it may suit your needs. Good luck!!