Variable file names

[Paul Peterson - Velox Consulting, LLC]

I have a formula that references another excel workbook. The name of this
workbook will be different depending on the date. Yesterday, I summed
numbers from C:/Testing/Errors - 021609; today I will sum numbers from a
different workbook named C:/Testing/Erros - 021709. In my worksheet, I have
to manually change the reference to reflect the current date. Is there a way
to make that name variable based on today's date? Any help would be
appreciated.

Paul Peterson
Velox Consulting, LLC

 

[Sheeloo]

Use a formula to get the string you use in your formula in a cell... say A1
then use this in B1
=INDIRECT(A1)

Once you get that working then you can replace A1 in the above with the
formula (without the = ofcourse)...

Your string formula should look something like
="'[C:/Testing/Erros - " & text(today(),"mmddyy") & "]Sheetname!'A1:A100"

 

[Paul Peterson - Velox Consulting, LLC]

Outstanding - thanks.

Use a formula to get the string you use in your formula in a cell... say A1
then use this in B1
=INDIRECT(A1)

Once you get that working then you can replace A1 in the above with the
formula (without the = ofcourse)...

Your string formula should look something like
="'[C:/Testing/Erros - " & text(today(),"mmddyy") & "]Sheetname!'A1:A100"

I have a formula that references another excel workbook. The name of this
workbook will be different depending on the date. Yesterday, I summed
numbers from C:/Testing/Errors - 021609; today I will sum numbers from a
different workbook named C:/Testing/Erros - 021709. In my worksheet, I have
to manually change the reference to reflect the current date. Is there a way
to make that name variable based on today's date? Any help would be
appreciated.

Paul Peterson
Velox Consulting, LLC

 

[Shane Devenshire]

Hi,

One comment - INDIRECT only works if the referenced file is open.


--
If this helps, please click the Yes button

Cheers,
Shane Devenshire

Outstanding - thanks.

Use a formula to get the string you use in your formula in a cell... say A1
then use this in B1
=INDIRECT(A1)

Once you get that working then you can replace A1 in the above with the
formula (without the = ofcourse)...

Your string formula should look something like
="'[C:/Testing/Erros - " & text(today(),"mmddyy") & "]Sheetname!'A1:A100"

I have a formula that references another excel workbook. The name of this
workbook will be different depending on the date. Yesterday, I summed
numbers from C:/Testing/Errors - 021609; today I will sum numbers from a
different workbook named C:/Testing/Erros - 021709. In my worksheet, I have
to manually change the reference to reflect the current date. Is there a way
to make that name variable based on today's date? Any help would be
appreciated.

Paul Peterson
Velox Consulting, LLC

 

[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.

 

[tjfwestcoast]

Hey Dave,
I pulled down the funcustomize.dll file from
http://xcell05.free.fr/english/index.html but I am having trouble
understanding how to hook it into my Excel 2003. I unzipped the file and
copied the contents to my C:\Documents and Settings\user\Application
Data\Microsoft\AddIns directory. In Excel I selected Tools>Add-ins and
selected the Funcustomize option. However, when I go to insert a function I
don't see any of the funcustomize functions. Is there something else I need
to do to see these?

Thanks for your help.

 

[Dave Peterson]

And if you start typing:
=indirect.ext(
and hit ctrl-shift-A, do you get any help?

 

[T. Valko]

I pulled down the funcustomize.dll file

I think you grabbed the wrong file. You want the MOREFUNC.XLL file.

 

[Dave Peterson]

I didn't.

But thanks for the correct response Biff.