We have a booking package issued by our corporate office, and we have a local PM file we use to mange our construction projects within our office. Obviously, the data in the PM file is originally sourced from the Booking Package, however the data within the booking package is across multiple worksheets and in the PM file most of that same data is located on a single sheet. In addition, the data from these files are then uploaded into a "master projects flow log" which was milestone dates within it.
Traditionally, the salesman has re-entered the data one by one into the PM File and then again into the PM Log, and this is taking and hour or so to do... so I want to speed this up, alot....
So what I need help with is:
Getting a button (or something) to browse to the respective file and load that path into a specific cell and use that cell as a part of the formula for the cells to source that data from.
What I'm envisioning is a dedicated worksheet "Booking" in the PM File, that has 2 columns of data: Column B is the data names and Column C is the actual data sourced from various cells from within the Booking Package. Then use a button to browse and select the respective booking package file, and this button will then save the file path into a cell E5 (doesn't matter where really). Then in cells C10:C50, have their formula use the filepath in E10 to populate its cell from their respective cells from within the booking package.
So essentially cell C10 formula is: booking file path|worksheet|cells data source or =E10[booking file path]'SalesEst(FAS)'!$P$24)
So once the booking package path has been selected, those cells in C10:C50 are then automatically populated.
Also, can we do this w/o having to open the Booking Package first, can we just get the data?
Finally i would simply use another button to copy C10:C40 and Paste/Value to break those links so its raw data and not "linked" data again.
Ive got the formulas in cells C10:C40 all set up, but what I'm missing is how to get a button to "browse" to the file needed and load that path into a that cell. I'm sure its simple enough, Im just dont know how to do it.
That what i envisioned, but if someone has anther idea, let me know.
Thanks in advance.
Brian.
Traditionally, the salesman has re-entered the data one by one into the PM File and then again into the PM Log, and this is taking and hour or so to do... so I want to speed this up, alot....
So what I need help with is:
Getting a button (or something) to browse to the respective file and load that path into a specific cell and use that cell as a part of the formula for the cells to source that data from.
What I'm envisioning is a dedicated worksheet "Booking" in the PM File, that has 2 columns of data: Column B is the data names and Column C is the actual data sourced from various cells from within the Booking Package. Then use a button to browse and select the respective booking package file, and this button will then save the file path into a cell E5 (doesn't matter where really). Then in cells C10:C50, have their formula use the filepath in E10 to populate its cell from their respective cells from within the booking package.
So essentially cell C10 formula is: booking file path|worksheet|cells data source or =E10[booking file path]'SalesEst(FAS)'!$P$24)
So once the booking package path has been selected, those cells in C10:C50 are then automatically populated.
Also, can we do this w/o having to open the Booking Package first, can we just get the data?
Finally i would simply use another button to copy C10:C40 and Paste/Value to break those links so its raw data and not "linked" data again.
Ive got the formulas in cells C10:C40 all set up, but what I'm missing is how to get a button to "browse" to the file needed and load that path into a that cell. I'm sure its simple enough, Im just dont know how to do it.
That what i envisioned, but if someone has anther idea, let me know.
Thanks in advance.
Brian.
Last edited:
