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


"JE McGimpsey" wrote:

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?


In article ,
ed9213 wrote:

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.