Excel 2002 - Formulas containing Invalid External Reference

M

mulr1966

ok well I got 1 thing conquored only to run into something else abou
Formulas and substituion variables.
my question is this I want to
change all the occurances of pieces of the formul
COUNTIF('PATHVALUE[NEW_Transfer_LOG_CLOSED.xls]Octobe
2003'!$F$26:$F$197,"OPD FDR")
with
COUNTIF('PATHVALUE[MMMM_YYYY_ts.xls]SHEETNAMEVALUE'!$F$26:$F$197,"OP
FDR")

I am encountering the following message:
The Formula contains an Invalid External Reference to a woorksheet.

I want to force this because what I am trying to do is change the 30
plus formulas to be automated on the fly by MACROS substituting th
Variable names with actual PATHNAMES , WOORKBOOK and WORKSHEET NAMES
so that I do not have to modify the report every month.

I did change C:\Transfer_reports\Robert_old\ to PATHNAME but had t
hit the CANCLE Button about 900 times because most of the formula
reference pathname multiple times. an easier way has to be there, An
help would be appreciated .
Thank you
Rober
 
D

Dave Peterson

How about if you keep the "formulas" text.

Then have your macro replace all the strings and also make it a formula.

Instead of starting with =, try starting $$$$$=.

Then do your replaces and include one more--change $$$$$= to =.

But be careful--make sure all those names are correct!

mulr1966 < said:
ok well I got 1 thing conquored only to run into something else about
Formulas and substituion variables.
my question is this I want to
change all the occurances of pieces of the formula
COUNTIF('PATHVALUE[NEW_Transfer_LOG_CLOSED.xls]October
2003'!$F$26:$F$197,"OPD FDR")
with
COUNTIF('PATHVALUE[MMMM_YYYY_ts.xls]SHEETNAMEVALUE'!$F$26:$F$197,"OPD
FDR")

I am encountering the following message:
The Formula contains an Invalid External Reference to a woorksheet.

I want to force this because what I am trying to do is change the 300
plus formulas to be automated on the fly by MACROS substituting the
Variable names with actual PATHNAMES , WOORKBOOK and WORKSHEET NAMES,
so that I do not have to modify the report every month.

I did change C:\Transfer_reports\Robert_old\ to PATHNAME but had to
hit the CANCLE Button about 900 times because most of the formulas
reference pathname multiple times. an easier way has to be there, Any
help would be appreciated .
Thank you
Robert
Click to expand...
 
M

mulr1966

Dave, Thanks again for your idea, I will try this and post back with th
results, I was banging my head with different solutions and had no
thought of that 1 , Thansk agai
 
Top