I'm trying to create a summary that pulls data from other workbooks, and I'm trying to come up with a less time consuming way to create that summary.
The specific data I'm referencing is in the same location in each of the other workbooks, so the name of the workbooks changes, but the cell locations are always the same.
Currently, I'm entering the names of each of the files I want to reference in column A of the summary. I want a formula that will take the name from column A and insert it into the workbook reference for specific cells.
So, for example, if column A has the name of an excel file called "Name1" and I wanted to reference cell B1 from that workbook, the formula would read "='C:\Documents\[Name1.xlsx]Sheet1'!$B$1"
I want the formula to pull whatever value is in Column A for that row, so something like "='C:\Documents\[A1.xlsx]Sheet1'!$B$1" (except that doesn't work). The goal is to fill in the file names in column A and then autofill the remainder of the cells.
Is there any way to make this work?
I'm excel '07
Thanks in advance!
The specific data I'm referencing is in the same location in each of the other workbooks, so the name of the workbooks changes, but the cell locations are always the same.
Currently, I'm entering the names of each of the files I want to reference in column A of the summary. I want a formula that will take the name from column A and insert it into the workbook reference for specific cells.
So, for example, if column A has the name of an excel file called "Name1" and I wanted to reference cell B1 from that workbook, the formula would read "='C:\Documents\[Name1.xlsx]Sheet1'!$B$1"
I want the formula to pull whatever value is in Column A for that row, so something like "='C:\Documents\[A1.xlsx]Sheet1'!$B$1" (except that doesn't work). The goal is to fill in the file names in column A and then autofill the remainder of the cells.
Is there any way to make this work?
I'm excel '07
Thanks in advance!