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.