Pulling data daily from a differently named report everyday

[ACG]

Hello,

I have a spreadsheet where I use formulas to pull data from multiple
reports on a shared server everyday. What I do is to save those
reports under a certain name everyday since the formulas have to have
the static report name in them to pull data. (For example, A1 may pull
data from c:\reportfolder\reportA, B1 may pull from c:
\reportfolder2\reportB, etc). In these report folders our IT group
runs a new report in it everyday but of course they change the name of
the report every day (to reflect the date) . . so in the reportfolder
there will be "reportA-3-12-2009", and "reportA-3-13-2009", and
"reportA-3-14-2009, etc). What I would like is to have a formula that
pulls it's data from the most recent one. Is it possible?

Thank you

 

[Dave Peterson]

The function you'd want to use that's built into excel is =indirect(). But that
function returns an error if the sending workbook is closed.

If that's a problem, then Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/
or
http://xcell05.free.fr/morefunc/english/index.htm

That includes =indirect.ext() that may help you.

 

[ACG]

The function you'd want to use that's built into excel is =indirect().  But that
function returns an error if the sending workbook is closed.

If that's a problem, then Laurent Longre has an addin (morefunc.xll) at:http://xcell05.free.fr/
orhttp://xcell05.free.fr/morefunc/english/index.htm

That includes =indirect.ext() that may help you.








--

Dave Peterson- Hide quoted text -

- Show quoted text -

The download doesn't seem to work on those pages. Any other ideas on
how to accomplish it?

 

[Dave Peterson]

Try searching Google for morefunc.xll.

But depending on the site you download from, you may not be getting the most
current version. I'd visit that site every couple of days looking to see if
it's ok.

 

[ACG]

Try searching Google for morefunc.xll.

But depending on the site you download from, you may not be getting the most
current version.  I'd visit that site every couple of days looking to see if
it's ok.







--

Dave Peterson- Hide quoted text -

- Show quoted text -

I've been testing the INDIRECT function just to se how it might work
once I got the add-in going and an issue that has come up is that, the
last name of the filename (the part that's changing daily) is the
date . . .added to the end of everyfilename in the format 2009-2-11
(for feb 11 2009). When I'm try to have INDIRECT add the different
parts of the filename from each of the cells I've typed them in, it
always brings in the date (for which I've just used NOW()-1 (to get
yesterday)) in the format of 432354354.1224343 instead of 2009-2-11,
even if I've formatted the cells with yyyy-mm-dd. Wont seem to change
no matter what I try.

 

[Dave Peterson]

=now() includes the date and time.
=Today() is just the date.

....&text(today()-1,"m-d-yyyy")&...

(modify the formatting string to what you need (mm and dd are two digit months
and years).)

(ps. I'm not sure what order the format is in, either. You seemed to change
from the original post.)

 

[ACG]

=now() includes the date and time.
=Today() is just the date.

...&text(today()-1,"m-d-yyyy")&...

(modify the formatting string to what you need (mm and dd are two digit months
and years).)

(ps.  I'm not sure what order the format is in, either.  You seemed to change
from the original post.)







--

Dave Peterson- Hide quoted text -

- Show quoted text -

Dave,

Thank you very much for your time and answers. It is working just
like you said. Unfortunately Morefunc keeps craching Excel and even if
I'm lucky enough for it not to, these formulas take so long to pull
the data it would be more effective to do it manually. I do sincerely
appreciate your time and knowledge Cheers

 

[Harlan Grove]

I have a spreadsheet where I use formulas to pull data from multiple
reports on a shared server everyday. What I do is to save those
reports under a certain name everyday since the formulas have to have
the static report name in them to pull data. (For example, A1 may pull
data from c:\reportfolder\reportA, B1 may pull from c:
\reportfolder2\reportB, etc). In these report folders our IT group
runs a new report in it everyday but of course they change the name of
the report every day (to reflect the date) . . so in the reportfolder
there will be "reportA-3-12-2009", and "reportA-3-13-2009", and
"reportA-3-14-2009, etc). What I would like is to have a formula that
pulls it's data from the most recent one. Is it possible?

I've had to deal with this over the years. The simplest way I've found
is copying the current day's file to a different location but with a
fixed filename, then have my formulas refer to the copied file using
the invariant filename. The file copies can be handled either by VBA
macros, plain old batch files or even manually.

 

[Dave Peterson]

And just add to Harlan's thoughts, copy the file to a local drive (like your
harddrive).

It should work faster from there than from a network drive (even if that is
local).

And if you see a slowdown, open that file first.

 

[ACG]

And just add to Harlan's thoughts, copy the file to a local drive (like your
harddrive).

It should work faster from there than from a network drive (even if that is
local).

And if you see a slowdown, open that file first.








--

Dave Peterson- Hide quoted text -

- Show quoted text -

Yeah, that's what I've been doing. Was just hoping to find a way where
I didn't have to do anything and the report would still update
everytime it was opened. BUt I can live doing it this way. Thank you
both very much!