Hello All,
At my work, we are trying to create a master workbook that drives a few slave workbooks (5 in total as of right now). Using Excel 2010
In the slave workbooks, the user is to select, from a drop down list, the customer and then the project that is being worked on for the customer. This list is supplied from the master workbook. We will then log how many hours are used on that project. The data that the user supplied (hours logged, notes, ect.) is then to be sent back to the master workbook where the admin of the workbook can take the data and do as he sees fit.
I have the input field for the user set up as a Table (as this is fairly easy to extend for the user, instead of doing a dynamic range) that is then named specific for the user. To get the data back to the master, I was going to import a pivot table to a sheet on the master, specific for that user.
I am also going to have a customer/projects sheet on the master that will have a table with the customer info on it. This table will then be pushed to a similar sheet on the slave workbooks. The catch here tho, is that I want this table to be editable by the slave workbook, and then pushed back to the master. It is currently setup as a Table.
The reason for the circle reference here, is so that the user can add new customers to the table without editing the master workbook, but user Y, who is also working on the same project as user X can log his time without creating multiple customers. If user X spells the customer name correctly, and user Y is not paying attention, and spells it incorrectly, then on the master workbook, there will be duplicate entries and erroneous data.
I can figure out how to get the pivot table to the master easily enough. But I do not know how to make the customer table to have the circle reference, assuming that it is even possible.
I would prefer not to use VBA if possible.
Any thoughts?
Thanks,
David
At my work, we are trying to create a master workbook that drives a few slave workbooks (5 in total as of right now). Using Excel 2010
In the slave workbooks, the user is to select, from a drop down list, the customer and then the project that is being worked on for the customer. This list is supplied from the master workbook. We will then log how many hours are used on that project. The data that the user supplied (hours logged, notes, ect.) is then to be sent back to the master workbook where the admin of the workbook can take the data and do as he sees fit.
I have the input field for the user set up as a Table (as this is fairly easy to extend for the user, instead of doing a dynamic range) that is then named specific for the user. To get the data back to the master, I was going to import a pivot table to a sheet on the master, specific for that user.
I am also going to have a customer/projects sheet on the master that will have a table with the customer info on it. This table will then be pushed to a similar sheet on the slave workbooks. The catch here tho, is that I want this table to be editable by the slave workbook, and then pushed back to the master. It is currently setup as a Table.
The reason for the circle reference here, is so that the user can add new customers to the table without editing the master workbook, but user Y, who is also working on the same project as user X can log his time without creating multiple customers. If user X spells the customer name correctly, and user Y is not paying attention, and spells it incorrectly, then on the master workbook, there will be duplicate entries and erroneous data.
I can figure out how to get the pivot table to the master easily enough. But I do not know how to make the customer table to have the circle reference, assuming that it is even possible.
I would prefer not to use VBA if possible.
Any thoughts?
Thanks,
David
