Excel problem - Reference another workbook using last week date as name

  • Thread starter Thread starter clambers
  • Start date Start date
C

clambers

I am trying to reference cells from another workbook which is named t
the date of the previous Wednesday ("yymmdd").

Ex. '040310.xls' for the 3/10/04 sheet.

Someone gave me this function: '=TODAY()-WEEKDAY(TODAY()-4)' t
calculate the date of the previous Wednesday, but I am unsure of how t
reference a filename based on this function.

Any help would be appreciated.
Thanks..
 
Hi
if the other workbook is opened try the following (won't work if the
other book is closed):
- in cell A1 enter the formula
=TEXT(TODAY()-WEEKDAY(TODAY()-4),"YYMMDD") & ".xls"

in B1 enter the formula
=INDIRECT("'[" & A1 & "]Sheet1'!A1")
this gets cell A1 form sheet1 of your referenced workbook.

If the other workbook is closed try the following: have a look at the
Add-In MOREFUNC.XLL
(http://longre.free.fr/english)
use the function INDIRECT.EXT.e.g.
=INDIRECT.EXT("''C:\temp\[" & A1 & "]Sheet1'!A1")
that is just replace your function INDIRECT with INDIRECT.EXT and add
the path information to your file

you may also have a look at the following thread (describing further
alternatives for accessing closed workbooks): http://tinyurl.com/2c62u
 
Thanks for the help, that worked out exactly as needed.

Now what if I want to do the same thing, but instead of a new workbook
it is just a worksheet within the same workbook that is named using th
same method.

Thank You
 
Hi
just use INDIRECT without the workbook reference part. e.g. if A1
contains a sheet name try
=INDIRECT("'" & A1 & "'!A1")
 
Thank you very much, you have been very helpful.

Is there any way to get the first method (with the seperate workbooks)
to work when the workbook is closed?

This sheet is going to be used on many computers, so I am not able to
download any add-in applications, there must be a way to accomplish
this with a standard excel formula.

Thank you again...
 
...
...
Is there any way to get the first method (with the seperate workbooks)
to work when the workbook is closed?

This sheet is going to be used on many computers, so I am not able to
download any add-in applications, there must be a way to accomplish
this with a standard excel formula.
Click to expand...
...

No, there isn't any way to accomplish this with standard Excel formulas.

See the tinyurl link in Frank's original response for alternatives. Note that if
you can't assume the presence of any add-ins, you can't use SQL.REQUEST. If you
can't use VBA, you're completely out of luck.
 
Hi
just see the first reply with the links I provided to you. There're
some alternatives though they all require either the use of VBA or
installing an add-in
 
Back
Top