Formula to use date in file name link reference

[DFruge]

I am stuck with this one and was hoping someone could help. I am
trying to figure out a formula that uses the date from a cell in the
file reference.
D2 contains: 02/01/09 (date format)
D7 contains the current formula:=SUM('G:\PATH HERE\Poker\[020109.xls]
POKER'!$H$16:$H$21)
I want the date portion of the formula "020109" to be replaced with
the date in D2. Each column has a different date. Any help anyone
can provide is greatly appreciated.

 

[Bernard Liengme]

You need to use the INDIRECT function
=SUM(INDIRECT("'G:\PATH
HERE\Poker\["&TEXT(D2,"ddmmyy")&".xls]POKER'!$H$16:$H$21"))

Your example date (02/01/2009) gave no clue as your date convention. My
formula is for the universal format dd/mm/yyyy; for the USA format please
change to TEXT(D2,"mmddyy")

best wishes

 

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

========
Ps. If D2 really contains a date, you'd want to format that date nicely:

=sum(indirect("'G:\PATH HERE\Poker\[" & text(d2,"mmddyy")
& ".xls]POKER'!$H$16:$H$21"))

(Untested)

Is 02/01/09
Feb 1, 2009
or
Jan 2, 2009
or
Sept 2, 2001
or
.....

Adjust that formatting string to what you need.

I am stuck with this one and was hoping someone could help. I am
trying to figure out a formula that uses the date from a cell in the
file reference.
D2 contains: 02/01/09 (date format)
D7 contains the current formula:=SUM('G:\PATH HERE\Poker\[020109.xls]
POKER'!$H$16:$H$21)
I want the date portion of the formula "020109" to be replaced with
the date in D2. Each column has a different date. Any help anyone
can provide is greatly appreciated.