| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula too long error
Have a formula that links other files to master file. Links must be updated
annually to look at new folder and file name within certain path. I'm not at liberty to shorten the folder or file names (company protocol). When I try to change the source under Edit -- Links, I get "Formula too long" error. This has not been the case in prior years. How can I get around this error? I have also tried Find/Replace to simply change the years, which would do the same as changing the source, but I still get "Formula too long" error. I need help on this one fast, as I have to provide worksheets to managers today! One formula is as follows: =+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-Bis.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-Corp All.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-DM.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-FW.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-JC.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-LS.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-LW.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-MH.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-SJ.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-Top Total.xls]2009 Budget'!$I7 Thanks! -- Audrey G. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula too long error
The first thing I would do is open the old files and the new linked files.
Then the formula will be much smaller (the path will be removed) and then try changing the links. If you don't like that, then I'd add a new worksheet (and hide it later). Instead of having that one long formula, I'd have individual cells in that hidden worksheet that did the retrieval from each workbook. Then I could use: =sum('hidden'!a:a) or put that sum in another cell on that hidden worksheet and just retrieve that: ='hidden'!b1 Audrey G. wrote: Have a formula that links other files to master file. Links must be updated annually to look at new folder and file name within certain path. I'm not at liberty to shorten the folder or file names (company protocol). When I try to change the source under Edit -- Links, I get "Formula too long" error. This has not been the case in prior years. How can I get around this error? I have also tried Find/Replace to simply change the years, which would do the same as changing the source, but I still get "Formula too long" error. I need help on this one fast, as I have to provide worksheets to managers today! One formula is as follows: =+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-Bis.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-Corp All.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-DM.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-FW.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-JC.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-LS.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-LW.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-MH.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-SJ.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-Top Total.xls]2009 Budget'!$I7 Thanks! -- Audrey G. -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula too long error
Since all you are doing is adding up some numbers try breaking the formula up
into 2 or 3 formulas and then add up the results. Alternatively you could make the individual cell references into named ranges and then add up the named ranges... Insert - Name - Define Create a name such as BisBudget and in the reference add 'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-Bis.xls]2009 Budget'!$I7 Next year you would just have to change the named range references... -- HTH... Jim Thomlinson "Audrey G." wrote: Have a formula that links other files to master file. Links must be updated annually to look at new folder and file name within certain path. I'm not at liberty to shorten the folder or file names (company protocol). When I try to change the source under Edit -- Links, I get "Formula too long" error. This has not been the case in prior years. How can I get around this error? I have also tried Find/Replace to simply change the years, which would do the same as changing the source, but I still get "Formula too long" error. I need help on this one fast, as I have to provide worksheets to managers today! One formula is as follows: =+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-Bis.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-Corp All.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-DM.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-FW.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-JC.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-LS.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-LW.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-MH.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-SJ.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-Top Total.xls]2009 Budget'!$I7 Thanks! -- Audrey G. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula too long error
Unfortunately, your first suggestion won't work for me. Again, because I
can't change the naming protocol, I have to leave the names of the current year files the same as the prior year files. So, opening both is not possible with Excel. I think I will have to see about adding the year to the file name which may allow me to use your first fix. I will try to the hidden worksheet idea to see how that goes. It wil ljust involve a tremendous amount of time to do this on my part. I have about 7 columns that will be involved in this process. I just don't understand why this error is occurring for the first time with this new year. I have always been able to just do the "change source" before today. Thanks for the prompt response! -- Audrey G. "Dave Peterson" wrote: The first thing I would do is open the old files and the new linked files. Then the formula will be much smaller (the path will be removed) and then try changing the links. If you don't like that, then I'd add a new worksheet (and hide it later). Instead of having that one long formula, I'd have individual cells in that hidden worksheet that did the retrieval from each workbook. Then I could use: =sum('hidden'!a:a) or put that sum in another cell on that hidden worksheet and just retrieve that: ='hidden'!b1 Audrey G. wrote: Have a formula that links other files to master file. Links must be updated annually to look at new folder and file name within certain path. I'm not at liberty to shorten the folder or file names (company protocol). When I try to change the source under Edit -- Links, I get "Formula too long" error. This has not been the case in prior years. How can I get around this error? I have also tried Find/Replace to simply change the years, which would do the same as changing the source, but I still get "Formula too long" error. I need help on this one fast, as I have to provide worksheets to managers today! One formula is as follows: =+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-Bis.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-Corp All.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-DM.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-FW.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-JC.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-LS.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-LW.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-MH.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-SJ.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-Top Total.xls]2009 Budget'!$I7 Thanks! -- Audrey G. -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula too long error
I may have breakup the formulas as you suggest (similar to Dave Peterson's
response). However, it is very time consuming to do this since I have about 7 columns that are invovled. As for the named range idea...how would that work with each year? I usually Save As the prior year file into the current year folder. This would make the named range the same between the two files. How would I update it to look at the new file? I haven't dealt much with named ranges. Thanks! -- Audrey G. "Jim Thomlinson" wrote: Since all you are doing is adding up some numbers try breaking the formula up into 2 or 3 formulas and then add up the results. Alternatively you could make the individual cell references into named ranges and then add up the named ranges... Insert - Name - Define Create a name such as BisBudget and in the reference add 'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-Bis.xls]2009 Budget'!$I7 Next year you would just have to change the named range references... -- HTH... Jim Thomlinson "Audrey G." wrote: Have a formula that links other files to master file. Links must be updated annually to look at new folder and file name within certain path. I'm not at liberty to shorten the folder or file names (company protocol). When I try to change the source under Edit -- Links, I get "Formula too long" error. This has not been the case in prior years. How can I get around this error? I have also tried Find/Replace to simply change the years, which would do the same as changing the source, but I still get "Formula too long" error. I need help on this one fast, as I have to provide worksheets to managers today! One formula is as follows: =+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-Bis.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-Corp All.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-DM.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-FW.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-JC.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-LS.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-LW.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-MH.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-SJ.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-Top Total.xls]2009 Budget'!$I7 Thanks! -- Audrey G. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula too long error
A much shorter formula is:
=SUM(A1:A10) where A1 thru A10 contain: ='Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-Bis.xls]2009 Budget'!$I7 ='Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-Corp All.xls]2009 Budget'!$I7 ='Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-DM.xls]2009 Budget'!$I7 ='Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-FW.xls]2009 Budget'!$I7 ='Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-JC.xls]2009 Budget'!$I7 ='Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-LS.xls]2009 Budget'!$I7 ='Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-LW.xls]2009 Budget'!$I7 ='Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-MH.xls]2009 Budget'!$I7 ='Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-SJ.xls]2009 Budget'!$I7 ='Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-Top Total.xls]2009 Budget'!$I7 -- Gary''s Student - gsnu200901 "Audrey G." wrote: Have a formula that links other files to master file. Links must be updated annually to look at new folder and file name within certain path. I'm not at liberty to shorten the folder or file names (company protocol). When I try to change the source under Edit -- Links, I get "Formula too long" error. This has not been the case in prior years. How can I get around this error? I have also tried Find/Replace to simply change the years, which would do the same as changing the source, but I still get "Formula too long" error. I need help on this one fast, as I have to provide worksheets to managers today! One formula is as follows: =+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-Bis.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-Corp All.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-DM.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-FW.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-JC.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-LS.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-LW.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-MH.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-SJ.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-Top Total.xls]2009 Budget'!$I7 Thanks! -- Audrey G. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula too long error
macros are not my strong suit...so how would i update this to pull from the
year and folder on an annual basis? -- Audrey G. "Don Guillett" wrote: One way using a macro to get data from closed workbooks. Make a list of the workbooks(without the extension) in a column (I used col 6) Sub getvaluesfromclosedusingformula() mc = 6 ' column F mysheet = "yoursheetnamehere"' Budget myrange = "I7" For i = 1 To 7 Cells(i, mc).Offset(, 1).Formula = _ "=[" & Cells(i, mc) & ".xls]" & _ mysheet & "!" & myrange & "" ms = ms + Cells(i, mc).Offset(, 1) Cells(i, mc +1).ClearContents Next i MsgBox ms range("yourdesiredrangehere").value=ms End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Audrey G." wrote in message ... Have a formula that links other files to master file. Links must be updated annually to look at new folder and file name within certain path. I'm not at liberty to shorten the folder or file names (company protocol). When I try to change the source under Edit -- Links, I get "Formula too long" error. This has not been the case in prior years. How can I get around this error? I have also tried Find/Replace to simply change the years, which would do the same as changing the source, but I still get "Formula too long" error. I need help on this one fast, as I have to provide worksheets to managers today! One formula is as follows: =+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-Bis.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-Corp All.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-DM.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-FW.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-JC.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-LS.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-LW.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-MH.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-SJ.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-Top Total.xls]2009 Budget'!$I7 Thanks! -- Audrey G. |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula too long error
Again, the file name is typed into the cellthe sheet is input as a variable and the cell is input as a variable. Figure it out. -- Don Guillett Microsoft MVP Excel SalesAid Software "Audrey G." wrote in message ... macros are not my strong suit...so how would i update this to pull from the year and folder on an annual basis? -- Audrey G. "Don Guillett" wrote: One way using a macro to get data from closed workbooks. Make a list of the workbooks(without the extension) in a column (I used col 6) Sub getvaluesfromclosedusingformula() mc = 6 ' column F mysheet = "yoursheetnamehere"' Budget myrange = "I7" For i = 1 To 7 Cells(i, mc).Offset(, 1).Formula = _ "=[" & Cells(i, mc) & ".xls]" & _ mysheet & "!" & myrange & "" ms = ms + Cells(i, mc).Offset(, 1) Cells(i, mc +1).ClearContents Next i MsgBox ms range("yourdesiredrangehere").value=ms End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Audrey G." wrote in message ... Have a formula that links other files to master file. Links must be updated annually to look at new folder and file name within certain path. I'm not at liberty to shorten the folder or file names (company protocol). When I try to change the source under Edit -- Links, I get "Formula too long" error. This has not been the case in prior years. How can I get around this error? I have also tried Find/Replace to simply change the years, which would do the same as changing the source, but I still get "Formula too long" error. I need help on this one fast, as I have to provide worksheets to managers today! One formula is as follows: =+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-Bis.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-Corp All.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-DM.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-FW.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-JC.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-LS.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-LW.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-MH.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-SJ.xls]2009 Budget'!$I7+'Z:\Finance\Budget & Financials\Business Plan\2009\Operating Exp\[Oper Exp-Top Total.xls]2009 Budget'!$I7 Thanks! -- Audrey G. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula to long error | Excel Discussion (Misc queries) | |||
| formula is too long error | Excel Worksheet Functions | |||
| Formula too long error... | Excel Discussion (Misc queries) | |||
| Error formula too long | Excel Worksheet Functions | |||
| formula too long error | Excel Discussion (Misc queries) | |||
Linear Mode
