Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Links to External Spreadsheet (using a variable in the link)
Hi,
We have a spreadsheet which is pulling in data from external spreadsheets. So we use the following type of function to do so: =([Budget.xls]Annual!C10:C25). However, the external spreadsheets we use are updated everyday. So each day, we have a new spreadheet named in a specific convention: FilenameDDMMYY.xls Is there a way to write the formula so that the DDMMYY part is dynamic, and updates according to the current date? We've tried working around this, but the way the function works, it doesn't seem to be able to take in a variable, and must be static. Thanks in advance! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Links to External Spreadsheet (using a variable in the link)
The function you need is INDIRECT.
-- David Biddulph "Gar" wrote in message ... Hi, We have a spreadsheet which is pulling in data from external spreadsheets. So we use the following type of function to do so: =([Budget.xls]Annual!C10:C25). However, the external spreadsheets we use are updated everyday. So each day, we have a new spreadheet named in a specific convention: FilenameDDMMYY.xls Is there a way to write the formula so that the DDMMYY part is dynamic, and updates according to the current date? We've tried working around this, but the way the function works, it doesn't seem to be able to take in a variable, and must be static. Thanks in advance! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Links to External Spreadsheet (using a variable in the link)
Hi David,
Thanks for the response, i've looked into using the INDIRECT function in Excel, but still haven't managed to get it working. I've tried a lot of variations to the following formula's: With & without extra ( ) brackets, and with and without =('H:\[=indirect("A9")]TEST123'!A13) =('H:\[=indirect(A9)]TEST123'!A13) =('H:\[indirect(A9)]TEST123'!A13) =('H:\[(indirect("A9"))]TEST123'!A13) A9 being the name of the .XLS spreadsheet, Test123 being the sub-worksheet and A13 the cell to read from. Effectively, I want Excel to read the above formula as =('H:\[file1.xls]Test123'!A13) But file1.xs is obviously the variable I would like to have. It's possible that this is not even technically possible, but I would like to explore the possibility first, before giving up. Any other ideas, guys?? Regards, "David Biddulph" wrote: The function you need is INDIRECT. -- David Biddulph "Gar" wrote in message ... Hi, We have a spreadsheet which is pulling in data from external spreadsheets. So we use the following type of function to do so: =([Budget.xls]Annual!C10:C25). However, the external spreadsheets we use are updated everyday. So each day, we have a new spreadheet named in a specific convention: FilenameDDMMYY.xls Is there a way to write the formula so that the DDMMYY part is dynamic, and updates according to the current date? We've tried working around this, but the way the function works, it doesn't seem to be able to take in a variable, and must be static. Thanks in advance! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Links to External Spreadsheet (using a variable in the link)
=INDIRECT("'H:\["&A9&"]Test123'!A13")
-- David Biddulph "Gar" wrote in message ... Hi David, Thanks for the response, i've looked into using the INDIRECT function in Excel, but still haven't managed to get it working. I've tried a lot of variations to the following formula's: With & without extra ( ) brackets, and with and without =('H:\[=indirect("A9")]TEST123'!A13) =('H:\[=indirect(A9)]TEST123'!A13) =('H:\[indirect(A9)]TEST123'!A13) =('H:\[(indirect("A9"))]TEST123'!A13) A9 being the name of the .XLS spreadsheet, Test123 being the sub-worksheet and A13 the cell to read from. Effectively, I want Excel to read the above formula as =('H:\[file1.xls]Test123'!A13) But file1.xs is obviously the variable I would like to have. It's possible that this is not even technically possible, but I would like to explore the possibility first, before giving up. Any other ideas, guys?? Regards, "David Biddulph" wrote: The function you need is INDIRECT. -- David Biddulph "Gar" wrote in message ... Hi, We have a spreadsheet which is pulling in data from external spreadsheets. So we use the following type of function to do so: =([Budget.xls]Annual!C10:C25). However, the external spreadsheets we use are updated everyday. So each day, we have a new spreadheet named in a specific convention: FilenameDDMMYY.xls Is there a way to write the formula so that the DDMMYY part is dynamic, and updates according to the current date? We've tried working around this, but the way the function works, it doesn't seem to be able to take in a variable, and must be static. Thanks in advance! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Links to External Spreadsheet (using a variable in the link)
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. Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ That includes =indirect.ext() that may help you. ======== Try something like: =INDIRECT.ext("'c:\excel\[Filename" & text(today(),"DDMMYY") & ".xls]Annual'!c1") (all one line) (Change the path to match what you need.) But that's a guess. I don't use that addin. But lots of people do. If you have trouble getting it to work, post back with the drive/folder name and what you've tried. I'll bet you get help! ========== An alternative may be to just copy today's workbook to a name that never changes. Then the formula in excel won't have to worry about today's date. Gar wrote: Hi, We have a spreadsheet which is pulling in data from external spreadsheets. So we use the following type of function to do so: =([Budget.xls]Annual!C10:C25). However, the external spreadsheets we use are updated everyday. So each day, we have a new spreadheet named in a specific convention: FilenameDDMMYY.xls Is there a way to write the formula so that the DDMMYY part is dynamic, and updates according to the current date? We've tried working around this, but the way the function works, it doesn't seem to be able to take in a variable, and must be static. Thanks in advance! -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
External Links in Excel 2007 with variable source file | Excel Worksheet Functions | |||
External reference with variable workbook name? | New Users to Excel | |||
Create an external reference link with embedded variable | Excel Worksheet Functions | |||
variable in a link where the variable is the name of the sheet | Excel Worksheet Functions | |||
Using variable in external links | Excel Discussion (Misc queries) |