Formula Too Long, Update Link Name

JBrandt17

New Member
Joined
Jan 21, 2005
Messages
2
I have a production report for each employee here which links to a master production report. I have created 5 extra production reports saved as File A, File B, File C, etc. I now have a new employee and need to change the name of the source file from File A to their name. But when I try and change the change the source of the link to the new file name I get an error message "formula too long." How can I remedy this?
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

P Sitaram

Well-known Member
Joined
Jun 24, 2003
Messages
1,974
Some options:

1. The new file name can be restricted to the same number of characters as `File A'.

2. See if all the production reports can be moved to the same workbook, with the consolidaton on top. This avoids external links and makes consolidation easier.

3. If you need to keep those separate workbooks, try to have individual sheets referring to individual workbooks. This will invite all the problems associated with external links.

4. For alternatives 2 & 3, you can set up two permanent blank sheets, say, First and Last. Individual sheets can then be added and removed as needed, without any change to the formulas.

5. Write a macro to loop through the files and paste the values to the consolidation workbook. This will avoid all the external links and formula related problems while still retaining one workbook per employee structure. For examples of such code, search this board with criteria like `copy multiple workbooks' 'loop multiple workbooks' etc.
 

JBrandt17

New Member
Joined
Jan 21, 2005
Messages
2
Thank you for your advice. I may need to explain the problem a little better. When I created the five extra spreadsheets, they are identical to all the others, and are included in the formulas that add up on the Master Production spreadsheet. For instance, my formula will sum a column from spreadsheets named, Jane, John, Mike, Mary, File A, File B, File C, etc. When I new employee begins I need to take File A and rename it, but without interrupting the formula. So, when I rename the file, I then go into Edit>Links and try to "change the source" of File A to the new employee's name, I am then given the error message "formula too long." Is it not possible to change the name of a source file without destrying the formula in the destination file? Unfortunately I cannot put all of the spreadsheets in one workbook, because each employee keeps their production report on their computer. And in each employee's file is a tab for each month. Thank you again for your help!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,594
Messages
5,637,299
Members
416,963
Latest member
zazama

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top