PivotMeThis
Active Member
- Joined
- Jun 24, 2002
- Messages
- 346
This file is updated with new data once a week (by me) It is also updated by 6 people at least once a week. It is edited once a week by another individual.
How it works. I have a worksheet which comes from one of our systems. I run it once a week and copy this data over the existing data in my hidden worksheet. The next worksheet (ProjectData) is filled with formulas to gather specific project/contract information from the hidden worksheet and other formulas which calculate contract costs. The hidden worksheet contains about 1000 rows, the ProjectData sheet only contains 85 projects. These are projects which may have significant impacts to traffic and this information is being used on a website. There are multiple fields which must be updated each week by the 6 people mentioned above - this is information which does not reside in our databases. Other fields on this sheet are locked so they cannot be changed. And the worksheet is protected.
This file contains VBA and a couple of buttons for the editor. When text is changed the font turns blue so she can see what has changed. Once she's done editing she pushes a hidden button which turns the font back to black. Then there is another button that she pushes to copy specific rows of this data to another tab (MAP). This worksheet is used by our GIS team and used to populate the map that is on the website. This information is also being used by other offices for other performance measures and tracking information. Other code ensures that the file always opens to the ProjectData tab so that wrong worksheet is not inadvertently changed.
That being said, the 6 people in the field are complaining that they can't get in to do their work when they want to because one of the other 6 people are using it. I have been looking for a way to make this available but have run into trouble trying to use the "share workbook" function because of my macros. I also looked at SharePoint but found it would not work. I thought about Access and wondered if there was a way to link this information to a database with forms that could be filled out but have very limited Access experience and it didn't look like it would refresh the spreadsheet anyway.
I wonder if there are other options available to make it easier for the staff to use this file. I appreciate any help and/or advise you can give me.
Thanks
How it works. I have a worksheet which comes from one of our systems. I run it once a week and copy this data over the existing data in my hidden worksheet. The next worksheet (ProjectData) is filled with formulas to gather specific project/contract information from the hidden worksheet and other formulas which calculate contract costs. The hidden worksheet contains about 1000 rows, the ProjectData sheet only contains 85 projects. These are projects which may have significant impacts to traffic and this information is being used on a website. There are multiple fields which must be updated each week by the 6 people mentioned above - this is information which does not reside in our databases. Other fields on this sheet are locked so they cannot be changed. And the worksheet is protected.
This file contains VBA and a couple of buttons for the editor. When text is changed the font turns blue so she can see what has changed. Once she's done editing she pushes a hidden button which turns the font back to black. Then there is another button that she pushes to copy specific rows of this data to another tab (MAP). This worksheet is used by our GIS team and used to populate the map that is on the website. This information is also being used by other offices for other performance measures and tracking information. Other code ensures that the file always opens to the ProjectData tab so that wrong worksheet is not inadvertently changed.
That being said, the 6 people in the field are complaining that they can't get in to do their work when they want to because one of the other 6 people are using it. I have been looking for a way to make this available but have run into trouble trying to use the "share workbook" function because of my macros. I also looked at SharePoint but found it would not work. I thought about Access and wondered if there was a way to link this information to a database with forms that could be filled out but have very limited Access experience and it didn't look like it would refresh the spreadsheet anyway.
I wonder if there are other options available to make it easier for the staff to use this file. I appreciate any help and/or advise you can give me.
Thanks