Replacing part of a (too) long formula

[AB3]

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

 

[Tim879]

Instead of find / replace... try these 2 options.


1) Go to Edit->Links and select the file you want to replace, click
change source and select the new file
2) Open both files (the old "mother file" and the new one) then try
the file / replace. by opening the files, the full path is no longer
displayed in the formula.

 

[Don Guillett]

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)

 

[AB3]

Thanks Tim, grateful for your reply.

However, opening both files didn't work, as when I tried to update the
"child" spreadsheet it acted as if the mother file doesn't exist! I've
checked path names, file names, etc and can't see anything wrong. Do you know
why this might be the case?

Using Edit>Links came up with the same 'Formula too long' error too. How can
I make it shorter if it won't let me edit it?

Could moving the files to a different folder have caused this?

Many thanks,

AB3

 

[AB3]

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

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
(e-mail address removed)

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

 

[AB3]

Apologies for fragmented reply.

Think I know why it won't recognise the open mother file - there is a space
after the file name before the file extension (ie filename .xls) in the
formulas, and apparently not in the actual file name. I've tried to put a
space at the end but this doesn't work -

grateful for any ideas as to how to include a space at the end of a file name