Linking Excel workbooks using named ranges to create a pivot table

Is it possible to link data from 1 workbook to be housed on a shared drive using a named range for 2 columns (Name and Project) to a 2nd workbook that will be housed in a database?

I hope to use a named range so that any updates to the 1st workbook will automatically be reflected on the 2nd worksheet when the user opens the 2nd workbook.

My ultimate goal is to use the linked data in the 2nd workbook along with other like data (Name and Project) in the 2nd workbook to create a pivot table displaying all Projects for each Name.

Many thanks, in advance, for your guidance.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

This can be done using an ODBC DSN.

After creating your spreadsheet on the shared drive
In Excel 2007 select the Data Tab->From Other Sources->From Data Connection Wizard->ODBC DSN->Excel Files
Browse to your shared drive spreadsheet and select it
Select your named range and finish the wizard

The information should appear in your spreadsheet

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
radiantjoyAuthor Commented:
vincem1099 - you are brilliant, a genuis, and indeed, a MASTER. Thank you for sharing your expertise with me!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.