I have to make comparisons of data monthly with files whose data are in the
same positions within the worksheets, but whose values vary each month.

I created a template with links that will read a cell in a particular file
(i.e. ='[12-06 Ops Stmt.xls] Chicago"!$L$205) and if I manually copy and
paste links I can create the comparisons I need.

Every month the file name changes. I would like to create a cell in which I
enter the new filename and modify the formulas so that the formulas in the
cells pull that filename from that cell and populate my comparison sheet.

I think I explained that right.

Is it doable?

Thanks.

Ed

As long as the file is open, you could enter the filename in, say, cell
A1 and use:

=INDIRECT("'[" & A1 & "] Chicago'!L205")




In article ,
ed9213 wrote:

I have to make comparisons of data monthly with files whose data are in the
same positions within the worksheets, but whose values vary each month.

I created a template with links that will read a cell in a particular file
(i.e. ='[12-06 Ops Stmt.xls] Chicago"!$L$205) and if I manually copy and
paste links I can create the comparisons I need.

Every month the file name changes. I would like to create a cell in which I
enter the new filename and modify the formulas so that the formulas in the
cells pull that filename from that cell and populate my comparison sheet.

I think I explained that right.

Is it doable?

Thanks.

Ed

Thanks for the suggestion. It didn't work for me, though. The reference
file is open, but using that "INDIRECT" script returns an error in which A1
is blue. I surrounded the filename in A1 in quotes, and that didn't resolve
it either.

Ed


"JE McGimpsey" wrote:

As long as the file is open, you could enter the filename in, say, cell
A1 and use:

=INDIRECT("'[" & A1 & "] Chicago'!L205")




In article ,
ed9213 wrote:

I have to make comparisons of data monthly with files whose data are in the
same positions within the worksheets, but whose values vary each month.

I created a template with links that will read a cell in a particular file
(i.e. ='[12-06 Ops Stmt.xls] Chicago"!$L$205) and if I manually copy and
paste links I can create the comparisons I need.

Every month the file name changes. I would like to create a cell in which I
enter the new filename and modify the formulas so that the formulas in the
cells pull that filename from that cell and populate my comparison sheet.

I think I explained that right.

Is it doable?

Thanks.

Ed

Works here - did you include ".xls" in the file name? Does your
worksheet name really start with 4 spaces?

In article ,
ed9213 wrote:

Thanks for the suggestion. It didn't work for me, though. The reference
file is open, but using that "INDIRECT" script returns an error in which A1
is blue. I surrounded the filename in A1 in quotes, and that didn't resolve
it either

Well, I did include the .xls in the filename in A1. I took the 4 spaces from
the format Excel uses when I do this: Go to a cell that i want to reference,
click Copy, then paste a link to it in the comparison spreadsheet. The 4
spaces were part of the link formula.

Thanks.

"JE McGimpsey" wrote:

Works here - did you include ".xls" in the file name? Does your
worksheet name really start with 4 spaces?

In article ,
ed9213 wrote:

Thanks for the suggestion. It didn't work for me, though. The reference
file is open, but using that "INDIRECT" script returns an error in which A1
is blue. I surrounded the filename in A1 in quotes, and that didn't resolve
it either

Your question prompted me to search, and the sheet name does have spaces in
front of it, so the name would appear to slide right. It is a protected
sheet, so I can't remove the spaces. The file name has many words, which is
why I surrounded it with quotes, and tried parentheses, but with no success.
I even renamed the file with a one-word filename, and that didn't help either.

Thanks.

"ed9213" wrote:

Well, I did include the .xls in the filename in A1. I took the 4 spaces from
the format Excel uses when I do this: Go to a cell that i want to reference,
click Copy, then paste a link to it in the comparison spreadsheet. The 4
spaces were part of the link formula.

Thanks.

"JE McGimpsey" wrote:

Works here - did you include ".xls" in the file name? Does your
worksheet name really start with 4 spaces?

In article ,
ed9213 wrote:

Thanks for the suggestion. It didn't work for me, though. The reference
file is open, but using that "INDIRECT" script returns an error in which A1
is blue. I surrounded the filename in A1 in quotes, and that didn't resolve
it either

that would indicate that the spaces are part of the worksheet name. When
I set up two workbooks, one with a sheet named " Chicago". The
filename in A1 should be exactly the filename, with extension, no
quotes, no extra spaces. Like I said - works in my test books.

What error do you get in the cell with the formula?


In article ,
ed9213 wrote:

Well, I did include the .xls in the filename in A1. I took the 4 spaces from
the format Excel uses when I do this: Go to a cell that i want to reference,
click Copy, then paste a link to it in the comparison spreadsheet. The 4
spaces were part of the link formula.