I made research around the site for a similar post, but i can't seem to construct my own from them (I've almost no knowledge with excel macros). Please help

Say you have a workbook (named inventory1.xls) of 1 sheet of 5 columns (A - E). How can you transfer automatically all data from that workbooks single sheet to another workbook (named consolidated.xls), also in a single sheet. With the workbook (consolidated.xls) automatically updated when data is added in the source workbook (inventory1.xls).

Thank you very much.


This will be used to help a non-profit NGO to improve their inventory tracking.
Thank you very much again.

Hi virsojour,

Is there a reason both sets of data can't be kept in the same workbook (eg on different worksheets)? And what is the purpose of the data replication?

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Macropod,

I plan to link excel files between pcs in a network. If not possible through pcs in a network. At least it would be easier for users to select the only file they need to update, and not have to open the consolidated file and browse through sheets...

Thanks macropod

Hi virsojour,

Do the other PCs add/delete items on the inventory (eg as in adding/deleting managed furniture items), or simply adjust quantities (as in adding/deleting stationery from a store)? If they're adjusting quantities, do they work with their own stocks, or do they all add/delete quantities from a common pool?

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Macropod,

They will be adding items on the inventory, and sometimes delete also.
There is also the possibility of adjusting the quantities of items. Adjusting of quantities will simply be done on a common pool.

Thank you very much again macropod.

Hi virsojour,

What kind of inventories are you working with (eg individual furniture/machinery items or bulk items like stationery)? For either kind of inventory, it is undesirable to allow every user to simply add/delete inventory items on their own workbooks without some form of control over what can be added/deleted. if you don't have such controls, that can cause problems when differrent users describe the same items in different ways. For example, generating a consolidated inventory becomes very difficult.

For an inventory of individual items, you need to have a master list with a lookup table from which users can select item types to add/delete records as items are acquired disposed of. For an inventory of bulk items, it's best to have all items listed in a 'master' workbook only and to only allow the users to adjust their quantities.

The above considerations dictate the way data from the different workbooks should be linked. In both cases, the master lists of inventory items should reside in a 'master' workbook, to which the other workbooks are linked. There is no need to use macros for this part - it's simply a matter of using formulae to link the data in both sets of workbooks. For an inventory of individual items, the master workbook would require a macro that can interrogate the users' workbooks and tally the data for each defined item - the users' workbooks wouldn't require any macros. This updating could be done periodically, and wouldn't need all the workbooks to be constantly accessible via a network. For an inventory of bulk items, each users' workbook would require a macro that would update the quantities in the master workbook as items are moved into/out of store. It also means either setting up 'trusted locations' for these workbooks, or requiring users to enable macros whenever they open their inventory workbooks. This would only be practical if all the workbooks are constantly accessible via a network - otherwise, no one could be sure of what quantities were in stock at any time.

As you can see, we haven't even started with the coding yet - it's all a matter of getting the structure sorted out first. Mind you, once you get the inventory structure and management issues sorted out, you might also quite reasonably conclude that a database program like Access would be better suited to your needs - and you wouldn't need any macros.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]