Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Inserting variables within file names
Not sure if this can be done, but you never know. I have a spreadsheet which imports data from various other spreadsheets, using simple formuals like: =C:\folder\[spreadsheet.xls]Book1!$Y$12 However, from time-to-time, I need to change the names of the files, and add in new files. So I tried to a formula with something like: ="C:\folder\["&A5&"spreadsheet.xls]Book1!$Y$12" With A5 containing the change in file name (my spreadsheets would be something like applespreadsheet.xls, orangespreadsheet.xls etc.) This of course does not work. Does anyone know of a way I could insert a variable as part of the file name? Many thanks for your help. -- kestrel ------------------------------------------------------------------------ kestrel's Profile: http://www.excelforum.com/member.php...o&userid=19082 View this thread: http://www.excelforum.com/showthread...hreadid=552254 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Inserting variables within file names
The technique used here would be INDIRECTing, but that doesn't work with
closed workbooks. See http://makeashorterlink.com/?F2993260A for an alternative solution, it is VBA but it works. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "kestrel" wrote in message ... Not sure if this can be done, but you never know. I have a spreadsheet which imports data from various other spreadsheets, using simple formuals like: =C:\folder\[spreadsheet.xls]Book1!$Y$12 However, from time-to-time, I need to change the names of the files, and add in new files. So I tried to a formula with something like: ="C:\folder\["&A5&"spreadsheet.xls]Book1!$Y$12" With A5 containing the change in file name (my spreadsheets would be something like applespreadsheet.xls, orangespreadsheet.xls etc.) This of course does not work. Does anyone know of a way I could insert a variable as part of the file name? Many thanks for your help. -- kestrel ------------------------------------------------------------------------ kestrel's Profile: http://www.excelforum.com/member.php...o&userid=19082 View this thread: http://www.excelforum.com/showthread...hreadid=552254 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Inserting variables within file names
Hi, thanks. I have tried Laurent Longre's MOREFUNC.XLL using indirect.ext Here is my formula =INDIRECT.EXT("'C:\folder\["&I2&".xls]Book1!$Y$12") However, this returns a #VALUE! Is this the correct way to use the formula? Thanks -- kestrel ------------------------------------------------------------------------ kestrel's Profile: http://www.excelforum.com/member.php...o&userid=19082 View this thread: http://www.excelforum.com/showthread...hreadid=552254 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Inserting variables within file names
No idea I am afraid, I have never used it.
-- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "kestrel" wrote in message ... Hi, thanks. I have tried Laurent Longre's MOREFUNC.XLL using indirect.ext Here is my formula =INDIRECT.EXT("'C:\folder\["&I2&".xls]Book1!$Y$12") However, this returns a #VALUE! Is this the correct way to use the formula? Thanks -- kestrel ------------------------------------------------------------------------ kestrel's Profile: http://www.excelforum.com/member.php...o&userid=19082 View this thread: http://www.excelforum.com/showthread...hreadid=552254 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
File A and File B Together | Excel Discussion (Misc queries) | |||
EXCEL97 FILE NAMES TRUNCATED TO THE FIRST LETTER IN THE FILE NAME | New Users to Excel | |||
Blank File Names spinner | Excel Discussion (Misc queries) | |||
Import file names | Excel Discussion (Misc queries) | |||
Import file names into cells | Excel Worksheet Functions |