Dynamically linking several worksheets

[Guest]

Hi,

I am trying to set up a master financial template worksheet for multiple
programs that contains many small projects.

Each project has its own financial worksheet. On the program level, I need
to sum all the project financials into the program financial. However, each
program has different numbers of projects. Rarely do the programs have the
same project numbers.

The question is, how can I set up in the program worksheet in such way that,
all a program manager has to do is enter the name/location of each project
financial worksheet in the program master worksheet and have the program
masterworksheet dynamically include the individual project worksheets? (e.g.
if you put in 5 file names the spreadsheet would go out and get those 5 files
and sum the numbers, but if another one is added, it would take 6.)

Thanks!!

 

[Frank Kabel]

Hi
are these sheets in ONE file?

 

[Guest]

Typo: "Worksheets" should be "Workbooks", but the setup/solution should be
identical, except for the path.

Thanks!

 

[Guest]

no they are not.

each program and project financial files are individual templates stored in
the same directory.

Each project financial sheet contains 4 worksheet itself.

The program financial sheet has the same structure as the project financial
sheet, except it sums up all other project workbooks.

 

[Frank Kabel]

Hi
to be honest: Then Excel is the wrong tool IMHO. excel is not good
consolidating several files + worksheets. This is more a database
application.

 

[Guest]

Agreed. But since everyone has already used the excel template, it would be
too much to ask them to convert. Besides, the finance department would bulk
at the idea.

I was hoping there would exist some sort of set up scheme where I could
dynamically link all the files together without having the individually tweak
the program level worksheet.

Would it be any different if everything is in 1 file like you first suggested?

 

[Frank Kabel]

Hi
if everything would be in one file it would be esier BUT still the main
problem is if I understood your setup correctly that you need a kind of
'querying' all sheets/files for a specific project/program. If you like
you could email a test/sample file and I'll have a look at it how one
could automate it
please explain in your file/email exactly what you're trying to achieve
(e.g. give an example)

email: frank[dot]kabel[at]freenet[dot]de

 

[Christian R.]

Hi, my recent problem looks alike.
To see my partial solution and hopefully help me for a full solution, please
read new post:

linking the other .xls files

Best regards,
Christian

Hi
if everything would be in one file it would be esier BUT still the main
problem is if I understood your setup correctly that you need a kind of
'querying' all sheets/files for a specific project/program. If you like
you could email a test/sample file and I'll have a look at it how one
could automate it
please explain in your file/email exactly what you're trying to achieve
(e.g. give an example)

email: frank[dot]kabel[at]freenet[dot]de

--
Regards
Frank Kabel
Frankfurt, Germany

Agreed. But since everyone has already used the excel template, it would be
too much to ask them to convert. Besides, the finance department would bulk
at the idea.

I was hoping there would exist some sort of set up scheme where I could
dynamically link all the files together without having the individually tweak
the program level worksheet.

Would it be any different if everything is in 1 file like you first suggested?