"Steve_n_KC" wrote...
....
After cancelling out of it a couple hundred times, I finally paid attention
to a "Windows Navigation Box" that kept popping up every time that I would
change my formula. I can't get it to do it again but it looked just like
the
box that opens for a FileOpen, but it had a different message across the
top. Honestly I don't remember what it said but it was 2 words separated
by
a ":"
....

This happens when you copy formulas in workBOOK 1 with references to other
workSHEETS in workBOOK 1 into another workBOOK (workBOOK 2) that doesn't
have a workSHEET with the same name as the one in workBOOK 1. For example,
if you have a range containing several formulas like

=VLOOKUP(A!X99,A!$Y$99:$Z$1000,2)

in workBOOK 1 which contains a workSHEET named A, then you copy that range
into workBOOK 2 which lacks a worksheet named A, Excel will 'helpfully'
prompt you to change the invalid worksheet references to A to valid file
references, which is why it displays a file selection dialog for EACH
reference to A. That is, Excel interprets the A!X99 and similar tokens in
workBOOK 2 as EXTERNAL REFERENCES to a WORKBOOK named A. If you don't have
such a workBOOK (and it's very likely you don't), Excel believes it needs to
help you change the external references by making it easier for you to
select different FILES.

There may be no easy way to deal with this other than to create dummy
worksheet A in workbook 2 when you paste formulas into workbook 2, then use
Edit Replace to change the worksheet names to the appropriate ones for
workbook 2 and delete the dummy worksheet A. You may believe you do already
have a worksheet A in workbook 2, but Excel is far less helpful at detecting
stray spaces, so worksheet 'A' in workbook 1 is not the same as either
worksheet 'A ' or ' A' in workbook 2.