Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Linking Workbooks using file names in a range
Looking to sum up several workbooks into one master consolidated book.
Want to have the list of names of files to sum up taken from a range, like: Col A Col B FILE1 Actual file1 name here (could change) FILE2 Actual file2 name here (could change) FILE3 Actual file3 name here (could change) The formula to sum the data is in each Cell on the master book (and is the SAME Cell as on the individual sheets, just taking the data from each one and summing it). Would look something like this: ='C:\[FILE1.xls]FINANCE'!$E9+'C:\[FILE2.xls]FINANCE'!$E9'C:\[FILE3.xls] FINANCE'!$E9 The File names could change but it shouldn't matter because the link is looking for the range names FILE1, FILE2, FILE3. It doesn't seem possible to get the links to work when the source is a range name. Any ideas? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Linking Workbooks using file names in a range
check out indirect()
=indirect("'C:\["&B1&"]Finance'!$E9+... "Mark T" wrote: Looking to sum up several workbooks into one master consolidated book. Want to have the list of names of files to sum up taken from a range, like: Col A Col B FILE1 Actual file1 name here (could change) FILE2 Actual file2 name here (could change) FILE3 Actual file3 name here (could change) The formula to sum the data is in each Cell on the master book (and is the SAME Cell as on the individual sheets, just taking the data from each one and summing it). Would look something like this: ='C:\[FILE1.xls]FINANCE'!$E9+'C:\[FILE2.xls]FINANCE'!$E9'C:\[FILE3.xls] FINANCE'!$E9 The File names could change but it shouldn't matter because the link is looking for the range names FILE1, FILE2, FILE3. It doesn't seem possible to get the links to work when the source is a range name. Any ideas? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Linking Workbooks using file names in a range
On Jul 13, 1:04 pm, bj wrote:
check out indirect() =indirect("'C:\["&B1&"]Finance'!$E9+... .... You forgot the closing double quote and the right parenthesis. But more fundamentally, INDIRECT doesn't work unless the other files are open. Excel can't load multiple files with the same base filename, so OPEN files NEVER need their drive/directory paths preceding their base filenames. If the OP needs the drive/directory paths, then the OP's files aren't open when being accessed, and in that case INDIRECT won't work. For alternative workarounds, see http://groups.google.com/group/micro...443753560f0075 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Linking to Workbooks with Constantly Changing Names | Excel Discussion (Misc queries) | |||
Linking Cells to a file that changes names | Excel Discussion (Misc queries) | |||
How to show file path in formual bar when linking workbooks? | Excel Discussion (Misc queries) | |||
Why do minimized workbooks in Excell not show file names? | Excel Discussion (Misc queries) | |||
Why do minimized workbooks in Excell not show file names? | Excel Discussion (Misc queries) |