I am trying to simplify a complex series of formulas by using a name for the
filename I want to open. That way if I ever need to change the path to the
file or or the filename it self all I have to do is change it in one spot.

My issue is that I can get it to work for a simple formula and pull a single
value. but if I try to use it in a more complex formula it is giving me #ref
error. I have tried many different things and it just will not work the way I
want. Here is an example of what I mean.

I have a worksheet called setup.
in Setup I changed cell c13's name to KAT

now in worksheet WK1 If I use this I can get a value ='[KAT]WK1 Recap'!H76

But what I really want to do is use this in a larger formula
like this

=IF(('[KAT]WK1
Recap'!$H$76)=0,0,COUNTIFS('[KAT]WK1'!$G$9:$G$206,"0",'[KAT]WK1'!$D$9:$D$206,A2)/'[KAT]WK1 Recap'!$H$76)

But when I do I get an #ref error when I step through the formula on the
first part which is esentially the same thing that I got a value when I
tested it by itself.




This One works if I type the file name out by it self. But makes it more
difficult if I want to change something later.

=IF(('C:\Users\Customer\Documents\[KAT - March.xls]WK1
Recap'!$H$76)=0,0,COUNTIFS('C:\Users\Customer\Docu ments\[KAT -
March.xls]WK1'!$G$9:$G$206,"0",'C:\Users\Customer\Documents \[KAT -
March.xls]WK1'!$D$9:$D$206,A2)/'C:\Users\Customer\Documents\[KAT -
March.xls]WK1 Recap'!$H$76)


Any help would be appreciated. I am using Excel 2007