Nested String Filename Variable

[ed9213]

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

 

[JE McGimpsey]

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

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

 

[ed9213]

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

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

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

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

 

[JE McGimpsey]

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

 

[ed9213]

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.

 

[ed9213]

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.

 

[JE McGimpsey]

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?

 

[ed9213]

The editor shows "INDIRECT(ref_test, [a1])" , and highlights the A1 in the
formula in blue, under the cell where the formula resides. I have tried to
populate cell A1 with the filename alone, and with the full path, with no
success.

 

[JE McGimpsey]

Not sure what "editor" you're referring to, nor what "ref_test" refers
to.

INDIRECT takes two arguments - the first is the string comprising the
reference, and the second is either TRUE (A1-style reference) or
FALSE(R1C1-style reference). If there is no second argument, TRUE is
assumed.

You still don't say what "no success" or "doesn't work" means. Do you
get a #REF error? A #VALUE! error?

What is the exact entry you're making?

 

[ed9213]

The editor I mentioned is the one that Excel pops up to be helpful with
formula creation. When I typed the string you sent, a message box poped up
letting me know that there was an error in my formula, and I could choose
Help, or OK to continue, or if I wasn't really trying to write a formula, I
should try again, or something like that. It also showed the syntax of the
INDIRECT command, which is what I listed in my last message.

A #REF error is left in the box, if I hit enter and Excel accepts it.
Otherwise, it will not allow me to close the cell because there is an error,
and usually it highlights the A1 part of the text in between the ampersands
(& A1 &). I took that to mean the value in Cell A1, and I tried variations
of the file name, with and without path, with and without single quotes,
double quotes, parentheses, etc., the file was always open, and no success
meant I never got the value of the cell I was naming to show up as the result
of my formula. The only times that I have had the proper result was by
copying and pasting a link.

So, I said to myself, if the filename changes, why not just use CTRL-H and
change all instances of the last filename to the new filename in the cells I
had pasted the links. The result there was that a pop-up box appeared
telling me that there was an incorrect value in a formula on this worksheet
and I couldn't continue.

I'm pretty sure this is a scenario that is a common one, and the only glitch
should have been the possible padding of the worksheet names with spaces, but
I created a test1.xls without spaces in the worksheet names, and that never
worked either.

I've had enough for tonight, and I'm going to sleep. Maybe something will
be clearer tomorrow.

Thanks for your help and for reading the tale of my frustration.

Ed

Not sure what "editor" you're referring to, nor what "ref_test" refers
to.

INDIRECT takes two arguments - the first is the string comprising the
reference, and the second is either TRUE (A1-style reference) or
FALSE(R1C1-style reference). If there is no second argument, TRUE is
assumed.

You still don't say what "no success" or "doesn't work" means. Do you
get a #REF error? A #VALUE! error?

What is the exact entry you're making?

The editor shows "INDIRECT(ref_test, [a1])" , and highlights the A1 in the
formula in blue, under the cell where the formula resides. I have tried to
populate cell A1 with the filename alone, and with the full path, with no
success.