Use Excel Cell as input for formula 1

[nja5150]

I would like to use something typed into a cell within excel to update other formulas within the spreadsheet.

Basically I want to be able to update a group of formulas by changing a single cell. Is this assigning a variable or defining a name?

This is probably really simple.

I have one summary worksheet that has 5 years of data on it. I want to be able to update each years group of formulas by simply assigning the cell references to a new file name, rather than manually updating each formula with the new file name.

Hope I'm making sense.

2004 4th quarter worksheet data
--------------------------------
2k4q4.xls

2004 summary work sheet
------------------------
2k4summary.xls
A1= 2k4q4.xls <- name it 2k4q4file

A2= c:\(2k4q4file)[A3+A5]

I know I'm close, just can't think of the specifics to change the formula the way I want.

Thanks for any help!

 

[AnotherHiggins]

You are close!

Check out the indirect function.

A2: [COLOR=blue white]= indirect(a1&"!A3")+indirect(a1&"!A5")[/color]

[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886

 

[nja5150]

This works great as long as the referenced workbooks are opened. Is there any way for the cell references to be assigned .xls files that are not currrently opened? I am aware of the links command in the edit menu. I'd like to be able to simply type a name of the file rather than drill through my directories.

Thanks for any help!