Hi Don, thanks for your reply.

Do you know if this would update automatically? At the moment any time I try
to edit any part of the formula it comes up with the 'Formula too long' error.

The destination workbook has about 20 tabs, and the workbook I'm trying to
update has hundreds of cells that use about 10 calculations each (from
varying worksheets in the destination workbook).

Unfortunately my experience of Excel falls far short of the person who
created this monster ;)

All advice gratefully received!

AB3

"Don Guillett" wrote:

You can define a name in your destination workbook such as myfile5
='C:\yourfolder\[yourfile.xls]yoursheet'!$A$7:$C$106
and refer to that in your formula

=VLOOKUP(F27,myfile5,2,0)
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"AB3" wrote in message
...
Hi,

I've taken over a project that consists of a number of Excel spreadsheets
that produce graphs from the data in a 'mother' spreadsheet (there are a
lot
of graphs and a lot of data!).

In order to update the data in the graphs spreadsheets, I have to 'Find
and
Replace' part of the formulas - ie changing the filepath so it points at a
different mother file. However, I keep getting a "Formula is too long"
error
message - is there any way round this?

I've tried moving the files so they had a shorter file path, but it is
still
too long (there are a lot of calculations). Changing the name of the new
mother file doesn't help either, as it's the old name that's too long.

Would be very grateful if somebody could help me with this as there are
far
too many of them to change by hand!

Thanks and regards,

AB3