Linking Excel to multiple workbooks 1

[aiden]

Hi,
I've been asked to create a workbook that will contain monthly summaries of 4 workbooks. My master workbook will contain 12 worksheets - one for each month of year. Each worksheet will summarize information from 4 product workbooks.

Since most of the product workbooks don't exist yet, I've created a browse window that allows the user to browse out the necessary files as they are created and insert the path and filename into a separate sheet in my master workbook.

Unfortuneately, I can't seem to create working links using the path + filename.

Does anyone have any suggestions on how to create links to other workbooks by referencing a cell that contains a path and filename?

Thanks in advance.

Aiden

 

[SkipVought]

Aiden,

PLEASE read

http://www.alphabetcitydataworks.com/FundamentalsOfRelationalDatabaseDesign.doc

You have the unique opportunity, since you are designing the system, to do it right. Segmenting your data into sheets, "one for each month of year", would be a tragic mistake.

Do you have to have "4 product workbooks"??? That would be a HUGE mistake too!

Please rethink your database design. Post back with detail to get some tips on specifics.

If you create ONE workbook containing your data - -each sheet a separate TABLE containing similar data, like PRODUCTS, you can query yor Excel database using MS Query (built-in functionality) using Data/Get External Data/New Database Query - Excel Files -- YOUR EXCEL DATABASE WORKBOOK ...

The query table is stored in Excel and can be Data/Refreshed any time you like. This is one of the ideal ways of reporting live data results from a database. The possibilities are ENDLESS!

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[aiden]

Hi Skip,

I was trying to keep things simple in my example.

Every month, we receive 4 different reports from siloed billing applications. Each bill is for a separate product line. The employee wants a summary of these reports :column1 - report1, column2 - report2, etc. They want to see each month on separate worksheets, so that they will be able to easily compare month over month costs.

Each report will have a different name than the previous report (ex. Report1-Jan.xls, Report1-Feb.xls, etc.) - the workbooks will not be overridden because they are kept for historical purposes.

Which makes things a little more complicated.

Thanks for anymore advice you can offer.

Aiden

 

[SkipVought]

What you REPORT and how data is STORED are two different things.

If you segment your STORAGE -- SIMILAR data in DIFFERENT workbooks/tables/locations -- it makes it EXTREMELY DIFFICULT to report, especially when the boss asks for a report of data that resides in a variety of locations.

Your KISS approch will be the KISS of DEATH, believe me!

Better to devise a way to load the incoming data properly in a sound database structure, than to yield to the seemingly "simple" approch that WILL bite you in the end. I've been around the block a time or two, and SIMPLE is simply STUPID!

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[SkipVought]

Just to clarify...

if you want to store the REPORT for each month in 2006 on a separate sheet, I have no problem.

if you want to store the DATA for each month in 2006 on a separate sheet, I have MONUMENTAL ISSUES with that!

You REPORT from an single database and then distribute/store any way at all. It's the DATABASE that must be intelligently designed and properly maintained. This is a valuable corporate asset!

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}