Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Reference / Copy Dynamic Data
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. -- mupps ------------------------------------------------------------------------ mupps's Profile: http://www.excelforum.com/member.php...o&userid=28454 View this thread: http://www.excelforum.com/showthread...hreadid=480564 |
#2
|
|||
|
|||
Reference / Copy Dynamic Data
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!! "mupps" wrote: 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. -- mupps ------------------------------------------------------------------------ mupps's Profile: http://www.excelforum.com/member.php...o&userid=28454 View this thread: http://www.excelforum.com/showthread...hreadid=480564 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions | |||
Using a relative SHEET reference for source data in a chart | Charts and Charting in Excel | |||
How do i copy columns of data in notepad into microsoft excel? | New Users to Excel | |||
How do I return an entire row of data from a reference array? | Excel Worksheet Functions | |||
Named dynamic ranges, copied worksheets and graph source data | Charts and Charting in Excel |