Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Replacing part of a (too) long formula
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Replacing part of a (too) long formula
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. On Jun 2, 7:17 am, AB3 wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Replacing part of a (too) long formula
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 EditLinks 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 "Tim879" wrote: 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. On Jun 2, 7:17 am, AB3 wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Replacing part of a (too) long formula
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Replacing part of a (too) long formula
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 "Tim879" wrote: 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. On Jun 2, 7:17 am, AB3 wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
replacing value in formula from outside worksheet? | Excel Discussion (Misc queries) | |||
Why does it take so long to delete a part of table? | Excel Discussion (Misc queries) | |||
help on replacing this formula | Excel Discussion (Misc queries) | |||
Replacing half a formula | Excel Discussion (Misc queries) | |||
replacing text within a formula | Excel Worksheet Functions |