On May 16, 5:20*pm, JW wrote:

Instead of copying and pasting the entire column of data, can you not
just copy A4 into B4, make the necessary formula corrections, and then
Autofill it to B200? *Of course, the absolute reference to the row
number would have to be removed, but that wouldn't be a big deal
according to the data given.

I suppose you are right, but that would mean diving deep inside the
code, where there be dragons...
Anyway I'm not the owner of this piece of wichcraft, I would have to
check with the owner if this wouldn't have any unwanted consequences.

All in all, this sounds like a very complicated deal. *Why not just
use a formula in B4 instead of VBA to do all of this? *Seems like an
nested Indirect reference to B1 to get the book name and assemble the
formula would work to me. *Something like:
=INDIRECT("[" & INDIRECT("B1") & "]Sheet1!A1")

I totally agree that it is waaaay too complicated! And you should see
the variable names and the comments, it's all in French! This thing
has to be used by people all over Europe and the official company
language is English. Aaargh! I'm frustrated, and it's only monday!
Do you really want to know why it was made that way or was that just a
retorical question?
Because if you want to know, it might take a few weeks before I know
the answer. I would have to ask the owner, who would have to ask the
guy he got it from, who would have to ask the original author, and the
last two people work at different plants, in another country.

I think part of the reason why a nested indirect reference isn't used
(except for the keyboard/chair thing), is that this particular
worksheet is generated by yet another piece of VBA code that I have
not yet dared to enter. It would mean that I would have to insert the
code for the nested indirect formula into the module that generates
the sheet. Oh, the humanity!
Another reason why this doesn't work: I have not told the entire
story. There is not one "template" formula, but two!
The value of the first formula is used as a cell comment, and the
value of the second formula is used as the actual cell value.
And yet another reason why I don't want to use Indirect to get the
book name: I don't trust Excel. I want to be 200% sure that it goes
looking in the correct directory for the workbooks. I think (correct
me if I'm wrong) that I can only do that if I use the full path, and
the only reliable method I know is in VBA. Sure, I could use the
INFO("directory") function, but if I'm well informed, the parameter
for the Info function is language dependent.

But thank you anyway for letting me pick your thoughts. I'm going to
use some of it.

--
Amedee