Link Data in Excel 2007 from work sheet to work sheet

Question

I have sheet to keep track of expiration dates. In one column I have an address and in another I have the sales person and in multiple others I have the expiration date (each month for up to 5 years has a column). I would like to be able able to list all address for each sales person in a different work book, as a bonus I would like to have the expiration dates pull over as well..

Answer 1

The basic formula nomenclature (at least it was in previous versions when I used this) is ='C:\folder\[other.xls]Sheet'!B5. As you can see, it's very similar to linking to other worksheets in the same workbook. You can use UNC paths instead of drive letters, so ='\\fileserver\share\[other.xls]Sheet'!B5 works, too. I don't know for sure if this still works because a lot of people had problems with it, but I don't see why they'd remove it instead of just deprecating it permanently (as MS tends to do).

That said, I would really avoid doing this if at all possible because broken links are a pain in the butt to deal with. I would instead combine all your worksheets into one workbook, and seriously consider moving to MS Access as it sounds like you're working with a sizable amount of relational data rather than just creating charts and tables. Excel can do ridiculous amounts of things and a power user can really do amazing things with it, but at some point it becomes unwise to keep treating a spreadsheet as a database.