Quick Links - Also see the menu above and more choices on the right side of the blog (too much, but all good stuff)

\/ ...and now BIMbuilder.com Blog Posts... \/

Wednesday, May 22, 2013

Excel Hyperlinks & Document Management Tricks

Do you have to keep track of a lot of different folders and files?
Do you wish you peers could open the correct Revit file?
Are you tired of having to tell people where to look for files?
Do you wish you could keep track of a project's active folders?

Well, here's a little tool for you. Excel can combine strings of text and it also has a hyperlink function. So, you can create a little project data spreadsheet and keep track of everything on a project without having to constantly search through folders.  At my last job, there was a project that had 1421 folders.  How the heck are you ever supposed to find something in less than 15 minutes if you're not the one managing the project.

Here's what you do.
1 Open Windows Explorer.
2 Go to a folder, any folder on your server.
3 While holding the Shift Key, Right Click on a file or folder
4 Now, let go of the Shift Key and Left Click on 'Copy as Path' 

As a bonus for you since you asked, if you click on one file or folder then  hold down the Control Key and click on other files and/or folders, then Shift+Right Click and 'Copy as Path', you can select multiple items at one time for the next step.

Next step is to open Excel.
Go to cell B1
Hold the Control Key and tap V (Ctrl+V = Paste)
Whatever you copied as path will now fill in on Column B in Excel.
In cells C1 and the cells below it, you can type in a friendly name for files and folders.

Now, for the final step,
Copy this formula:  

=HYPERLINK(IF(IFERROR(SEARCH("http",B1),0)+IFERROR(SEARCH("www",B1),0)=0,"file:///","")&B1,IF(B1<>"",IF(C1<>"",C1,B1),""))

Now, go to Excel and go to cell A1.
Now, copy what's in cell A1 and you can paste it from A2 down as far as you want to A100 or beyond

Column A will now have a list of files and/or folders with hyperlinks that will automatically open files or folders for you.

You can also add web pages in Column B.  For example, if you have a cloud upload folder on Box.com, you can open the project subfolder in Box, then copy and paste the URL to the Excel spreadsheet.  You can put your server folder right below that in the spreadsheet, so you can quickly open the folder where files need to be uploaded or downloaded, then open the Box subdirectory to sync your files.

You can get fancy later and hide Columns B & C or make a new Tab, cut Column A and paste it into the other tab.  You can also simply type mailto:name@domain.com in a cell in Column A for quick links to email people.  

My overall concept is that you have a spreadsheet for each project you have.
You can even have a master spreadsheet listing each project, job number and project manager/team and then have a page which each project folder that can open up the individual project folder and file hyperlink list to manage your project documents.

Ask your staff how much time they spend everyday trying to find files and then ask the people who occasionally go into project folders how long it takes them to find stuff.  

10 minutes a day searching is 43.33 hours a year.  Multiple that by $75/hour billable rate and you're losing $3250 a year per person.  If your firm has 50 people, you're only losing $162,500 a year in billable time.   

Let me know if the spreadsheet helps or you have problems with it.  


 ___________________________________________________________________________________________________________  

• Join the buildingSMART alliance - National BIM Standards Provider
• Are you getting your CM-BIM credential? Click here for classes and information
• Click here to Subscribe to the blog via daily email updates for the BIM news you don't want to miss.
___________________________________________________________________________________________________________

2 comments:

Anonymous,  May 27, 2013 at 11:03 AM  

Can you post up an spread sheets example.

Thanks

Alex May 28, 2013 at 9:35 AM  

Excellent tips! It will certainly help me manage my documents well and keep track of them. Never knew about finding out the links to the local documents in the machine.

  © Blogger template ProBlogger Template by Ourblogtemplates.com 2008

Back to TOP  

[Valid Atom 1.0]