I am creating a user interface that will eventually fill out the other associated sheets from the information provided. I am wondering if it is possible to have the sheets auto fill if they are unopened or stored in another computer/network file instead of being included in the same work book?
Referencing multiple sheets???
- Thread starter Hamish
- Start date
No, not workbooks.
First, I think you are talking about workbooks, not worksheets. XL workbooks are the files, which can contain multiple worksheets. If you do have multiple worksheets in a workbook, then yes, the other sheets will update as you make changes within the workbook. But if an external workbook is closed, there is no way for it to update w/o being opened and re-saved.
First, I think you are talking about workbooks, not worksheets. XL workbooks are the files, which can contain multiple worksheets. If you do have multiple worksheets in a workbook, then yes, the other sheets will update as you make changes within the workbook. But if an external workbook is closed, there is no way for it to update w/o being opened and re-saved.
Not in a good way. I've seen some instances where people use VB to setup multiple passwords, but it's tricky and not foolproof. Do your users need each other's data, or do only you need a summary? If the latter, there are lots of ways to build summary workbooks.
http://www.rondebruin.nl/win/section3.htm
http://www.rondebruin.nl/win/section3.htm
Jon von der Heyden
Member
Hi Hamish
What you are asking t accomplish is not for the faint-hearted and it will likely involve lots of effot. But the truth is 'yes, you can update an external and closed workbook'. It involves using ADO. This is in part explained here.
The more detailed tutorial on how to use ADO with Excel is here. The tutorial I am referring you to is a little old now but I still deem it the best tutorial on the web for this (that I know of)!
So it seems that you are trying to set-up a multi-user solution using Excel. Firstly it is important to understand that Excel is never a truly multi-user application. ADO is a technology used for interacting with databases. It is compatible with Excel data sources but generally speaking it is better to use a proper database. If you have MS Access at your disposal and you have a shared directory then I would rather recommend that you use MS Access and develop an Excel interface using ADO.
I suggest you do some reading and maybe make a start at trying to set-up an external data source. Then develop some input forms in Excel and we can help you with the code to write those inputs to your external data source. I have plenty of bits that I am happy to share with you once you get going...
Regards
Jon
What you are asking t accomplish is not for the faint-hearted and it will likely involve lots of effot. But the truth is 'yes, you can update an external and closed workbook'. It involves using ADO. This is in part explained here.
The more detailed tutorial on how to use ADO with Excel is here. The tutorial I am referring you to is a little old now but I still deem it the best tutorial on the web for this (that I know of)!
So it seems that you are trying to set-up a multi-user solution using Excel. Firstly it is important to understand that Excel is never a truly multi-user application. ADO is a technology used for interacting with databases. It is compatible with Excel data sources but generally speaking it is better to use a proper database. If you have MS Access at your disposal and you have a shared directory then I would rather recommend that you use MS Access and develop an Excel interface using ADO.
I suggest you do some reading and maybe make a start at trying to set-up an external data source. Then develop some input forms in Excel and we can help you with the code to write those inputs to your external data source. I have plenty of bits that I am happy to share with you once you get going...
Regards
Jon
Thanks Jon. Looks like I will have to up skill myself with access and SQL etc.
Luke - to answer your question... The process is as follows;
User 1 Fills in a sales estimate sheet, this in turn populates a contract document sheet, colour selection and job specification sheet etc. The information transferred will be client information and requests etc. The sheets can then be accessed by the relevant users allowing them to fill out their worksheets accordingly, hopefully saving some time by having auto-filled the boring bits.
User 2 will be able to access the contract docs and job spec sheet as well as a copy of the sales estimate, however they will be unable to change the original sales estimate without the correct permissions.
I hope this makes sense? Thanks for your help guys, it is greatly appreciated.
Luke - to answer your question... The process is as follows;
User 1 Fills in a sales estimate sheet, this in turn populates a contract document sheet, colour selection and job specification sheet etc. The information transferred will be client information and requests etc. The sheets can then be accessed by the relevant users allowing them to fill out their worksheets accordingly, hopefully saving some time by having auto-filled the boring bits.
User 2 will be able to access the contract docs and job spec sheet as well as a copy of the sales estimate, however they will be unable to change the original sales estimate without the correct permissions.
I hope this makes sense? Thanks for your help guys, it is greatly appreciated.