Changeing links source with VBA

[mduff]

I have a sheet with formulas that pull data off an other excel WB. The issue is that I don't create these workbooks and they are always coming in with a different name. I was wondering if any VBA code that I could press a button and browse to the source of the links and update them instead of every day hitting edit links change source etc

Any help is appreciated

[Jacob Hilderbrand]

Take a look at the Indirect function. Instead of using Sheet1 for example use

 =INDIRECT(A1 & "!A1")

.

A1 will hold the sheet name. Then just build your formula within the Indirect function.

For example if in A1 you type MySheet then the formula would evaluate to =MySheet!A1 and would then get the value from A1 in MySheet.

This way all you have to do is change the value in A1 whenever you want to refer to another sheet.

[mdmackillop]

To expand on Jake's comments, if you're dealing with another workbook (which must be open), the text in cell A1 would be in the form [Data.xls]sheet1
If you need your formula to be dynamic, you might use something like

=INDIRECT($A$1& "!A" & ROW()-11)

, depending upon your offset.

[mduff]

Thanks to all I'll check this out