Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to use date in file name link reference
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to use date in file name link reference
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 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "DFruge" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to use date in file name link reference
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. DFruge wrote: 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. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to remove the file link from the formula? | Excel Worksheet Functions | |||
How to link an Excel file due date to Outlook calendar date? | New Users to Excel | |||
Add reference to DDE Link formula | Excel Worksheet Functions | |||
formula link to a file | Excel Worksheet Functions | |||
Q: Can a formula reference a cell to get the file name to link to for data? | Excel Discussion (Misc queries) |