Hello friends,

Every morning our finance department creates a new spreadsheet for the
previous day's financials. I then take the information I need from the
spreadsheet and copy it over to new workbook that I keep upto date with my
departments daily numbers. I would love to "automate" this process by
settign up my spreadsheet to automatically link my department's sheet with
the appropriate cells on the finance department's sheet. The problem is
this: The finance reports are created new every morning, so there is no way
(that I know of) that I can tell my departmental spreadsheet to link to this
new document until it is created. the finance dept. creates this document in
the same folder every day and only changes the number at the end of the
report to reflect the date. This means that I can know the exact file path
for any day of the eyar so I could hypothetically setup my deptmental sheet
for the rest of the year. Is there a way to link the cells I need with a
document that doesn't yet exist? Does this make any sense?

No but maybe you can use a macro to create links to certain cells in files in a folder
http://www.rondebruin.nl/summary2.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl



"jrm" wrote in message ...
Hello friends,

Every morning our finance department creates a new spreadsheet for the
previous day's financials. I then take the information I need from the
spreadsheet and copy it over to new workbook that I keep upto date with my
departments daily numbers. I would love to "automate" this process by
settign up my spreadsheet to automatically link my department's sheet with
the appropriate cells on the finance department's sheet. The problem is
this: The finance reports are created new every morning, so there is no way
(that I know of) that I can tell my departmental spreadsheet to link to this
new document until it is created. the finance dept. creates this document in
the same folder every day and only changes the number at the end of the
report to reflect the date. This means that I can know the exact file path
for any day of the eyar so I could hypothetically setup my deptmental sheet
for the rest of the year. Is there a way to link the cells I need with a
document that doesn't yet exist? Does this make any sense?

what would such a macro look like? Since the files ren't created yet, how
would the macro I write now, find the file that won't exist until tomorrow?

"Ron de Bruin" wrote:

No but maybe you can use a macro to create links to certain cells in files in a folder
http://www.rondebruin.nl/summary2.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl



"jrm" wrote in message ...
Hello friends,

Every morning our finance department creates a new spreadsheet for the
previous day's financials. I then take the information I need from the
spreadsheet and copy it over to new workbook that I keep upto date with my
departments daily numbers. I would love to "automate" this process by
settign up my spreadsheet to automatically link my department's sheet with
the appropriate cells on the finance department's sheet. The problem is
this: The finance reports are created new every morning, so there is no way
(that I know of) that I can tell my departmental spreadsheet to link to this
new document until it is created. the finance dept. creates this document in
the same folder every day and only changes the number at the end of the
report to reflect the date. This means that I can know the exact file path
for any day of the eyar so I could hypothetically setup my deptmental sheet
for the rest of the year. Is there a way to link the cells I need with a
document that doesn't yet exist? Does this make any sense?

You can only run the macro when the files exist
No way to link to a file that not exist

The macro create very fast the links for when you need them

--
Regards Ron de Bruin
http://www.rondebruin.nl



"jrm" wrote in message ...
what would such a macro look like? Since the files ren't created yet, how
would the macro I write now, find the file that won't exist until tomorrow?

"Ron de Bruin" wrote:

No but maybe you can use a macro to create links to certain cells in files in a folder
http://www.rondebruin.nl/summary2.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl



"jrm" wrote in message ...
Hello friends,

Every morning our finance department creates a new spreadsheet for the
previous day's financials. I then take the information I need from the
spreadsheet and copy it over to new workbook that I keep upto date with my
departments daily numbers. I would love to "automate" this process by
settign up my spreadsheet to automatically link my department's sheet with
the appropriate cells on the finance department's sheet. The problem is
this: The finance reports are created new every morning, so there is no way
(that I know of) that I can tell my departmental spreadsheet to link to this
new document until it is created. the finance dept. creates this document in
the same folder every day and only changes the number at the end of the
report to reflect the date. This means that I can know the exact file path
for any day of the eyar so I could hypothetically setup my deptmental sheet
for the rest of the year. Is there a way to link the cells I need with a
document that doesn't yet exist? Does this make any sense?

Why not put a dummy workbook in that same folder.

Your workbook can link to that dummy workbook.

Then when the real workbook is created, you can use Edit|links to change the
source to the real workbook.

jrm wrote:

Hello friends,

Every morning our finance department creates a new spreadsheet for the
previous day's financials. I then take the information I need from the
spreadsheet and copy it over to new workbook that I keep upto date with my
departments daily numbers. I would love to "automate" this process by
settign up my spreadsheet to automatically link my department's sheet with
the appropriate cells on the finance department's sheet. The problem is
this: The finance reports are created new every morning, so there is no way
(that I know of) that I can tell my departmental spreadsheet to link to this
new document until it is created. the finance dept. creates this document in
the same folder every day and only changes the number at the end of the
report to reflect the date. This means that I can know the exact file path
for any day of the eyar so I could hypothetically setup my deptmental sheet
for the rest of the year. Is there a way to link the cells I need with a
document that doesn't yet exist? Does this make any sense?

--

Dave Peterson

I see. Thanks!

How would I create a macro that creates those links every morning? even
when I driect type the link to the correct file, I have to go through the
file browser to actually link the two files. Does that make sense? (thanks so
much for your help by the way!)

See the link I posted in my first reply


--
Regards Ron de Bruin
http://www.rondebruin.nl



"jrm" wrote in message ...
I see. Thanks!

How would I create a macro that creates those links every morning? even
when I driect type the link to the correct file, I have to go through the
file browser to actually link the two files. Does that make sense? (thanks so
much for your help by the way!)

so, you are saying that I Create a workbook that looks the exact smae as the
finance dept. one and link to that. Then every morning I go in and edit the
link? Is that right?

That's what I'm suggesting.

Same layout, same worksheet names, and maybe even some dummy data in it to make
the results of the formulas look "nice"--whatever that means.

jrm wrote:

so, you are saying that I Create a workbook that looks the exact smae as the
finance dept. one and link to that. Then every morning I go in and edit the
link? Is that right?

--

Dave Peterson

You can set up 'Text' formulas, with the proper path already set up to the
finance departments WBs (since you say you already know the names ahead of
time), and referencing the pertinent cells within those WBs.

Then, on a daily basis, just convert those 'Text' formulas (links) to actual
XL formulas and have the required data returned to your departments WB.

Check out this link to an old post on the subject:

http://tinyurl.com/8v8e5

The end of the thread is the pertinent post.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"jrm" wrote in message
...
so, you are saying that I Create a workbook that looks the exact smae as the
finance dept. one and link to that. Then every morning I go in and edit the
link? Is that right?