•2 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
How do I automate with excel by pulling files in my work shared network drive?
Our take
Automating your COI tracking sheet in Excel can greatly enhance efficiency for project managers monitoring insurance expirations. By pulling data from your shared network drive, you can streamline updates for each subcontractor’s COI files. This approach allows you to maintain an organized, color-coded visual of coverage statuses—green for good, yellow for renewal reminders, and red for expirations.
So this one is a doozy… I’ve been assigned to make an COI tracking sheet (certificate of liability). I am able to created an excel table by exporting a bunch of data from my legal team Sharepoint which contain information such as “subcontractor company name” “requestor” “subcontract #” “line of business” you get the idea.
The purpose of this tracking sheet is for the project managers can look up when their COI insurance(general, automotive, workers comp, etc…) is about to expire so they can get renewed. So I have a column for each type of coverage and they are all color coded (green = good, Yellow = about to expire, Red = expire) my main hiccup right now is figuring out to automate a way to pull information for the COI (ACCORD 25 form) so it can all update as it gets entered in
Note: this COI tracking sheet is going to live in an online share point(so excel online)
The COI files do not live in the share point they live a share network drive in a folder and the COI are in sub folders with each individual subcontractor.
Just looking for advice
Thanks!
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Is there any way to make a relational DB like thing in excel?For context, my work involves maintaining a lot of excel trackers. Basically we maintain these to track project details for a client like project deliverables, project codes (for employee clock-ins), project milestones, project assigned to PMs or not, etc - all in different excel files. This might sound like simple info, but we capture a lot of details related to project in all those files - like the main tracker will have bascially columns for capturing info from every section of the contract signed with client. The clock-in codes tracker will have its name, parent account ID, clock-in category, project ID, and a few other columns. Just adding one project's details to all trackers takes about 30-50 mins right now (depending on complexity and category of the project). However, maintaining multiple files leads to a lot of duplication effort - basically you add name of the project, project ID, PM name etc so many times. Anyway this can be changed? Like we add all data in one sheet and maybe pull it into different views for different purposes? I have done some research with gpt and on youtube, but they suggest going the power apps/ power BI way, but I am not too well-versed with those. And I was thinking if there is another solution that can be done in excel itself? Or if power BI is the way, then maybe can you guide me to a starting point for that? Thanks in advance. submitted by /u/WorldlyDot_1 [link] [comments]
- Looking for project tracking ideasHi, As the title suggests I’m trying to create an Excel sheet that tracks the progress of various projects. Essentially, I was given a messy excel document to look after. I don’t have particularly advanced skills and nor do I want to spend too much time on this. So… One sheet lists projects as rows and they all have a reference code. There is a column designated for “action updates” where people overwrite progress each month. Other columns exist for project status, dates etc. My idea was to create another sheet which also lists the projects but acts as an action/change history log. I attempted to have a drop down through grouped cells that would act as a historic list of all changes and action updates around each project. The row with the corresponding project number would act as a “live view”, so using an X look up to display this data on the original sheet. Is there a better way to create something like this? There must be loads of ways to do something like this, but I just can’t think which way to do it!! Thank you for any help!!! submitted by /u/Formal_Disk_3760 [link] [comments]
- Central Data Sheet efficiencyHoping that someone with some in depth technical knowledge of Excel can help me out with a query. We issue Finance Tracker spreadsheets to projects in our organisation, and then link them back to a central sheet that we use for monitoring and reporting. There are maybe 40-50 cells in each spreadsheet that we need to pull in to our central sheet, but they’re spread around the tracker and so are generally linked individually, and we’re now approaching 120 trackers that need to be linked, so the central sheet is starting to get quite slow to load and update. My question is this: When doing cross workbook cell look ups, do arrays work more efficiently than individual cell references, or is there no noticeable difference in runtime? My idea is to, in each project’s workbook, create an “export” tab that contains all the information that needs to be pulled out, and link the central sheet by looking at that array, rather than each individual cell. submitted by /u/Jamespg614 [link] [comments]
- Making a fillable form with dropdowns and sections that can be added and moved easily**SEE ATTACHED IMAGE** I am attempting to improve this spreadsheet. I have a decent amount of experience with Excel but I am unfamiliar with the terminology require to describe the exact functions I am attempting to implement. The function of the sheet is to log various statuses and information pertaining to ladders and their inspection status. Each workbook has a header section and a footer section to diffentiate between clients. The sections that detail the individual ladders need to be moveable / re-order more easily, and also be able to insert a blank ladder section for any new ladders that come into service. The reason excel was selected is for access reasons. All of our clients use excel, but if there is an objectively better tool for the job that is part of the Office suite that people can use via the web or with their Office licence then I will consider that. Thank you to everyone who is able to provide some insight into this. https://preview.redd.it/7ln3oocpcjvg1.png?width=1405&format=png&auto=webp&s=584525d55bd27779bd56a9d746b5dcc6dd913c70 submitted by /u/WillowDime [link] [comments]