HI All

I need to change a worksheet in the link I have created.

eg

In Cell A1 formula is:

=('C:\data\worksheets\team\[quality Scores.xls]Jan'!B2)

What I want to do is have a drop down list by Mth in B1 which wil
dynamically change the Worksheet name to whatever month is selected i
B1.

So if B1 = Mar

Then A1 will chnage to:
=('C:\data\worksheets\team\[quality Scores.xls]Mar'!B2)

So could I do this via a macro somehow.

The external workbook will be closed and needs to update this workboo
with all the current data

--
Mikeic
-----------------------------------------------------------------------
Mikeice's Profile: http://www.excelforum.com/member.php...fo&userid=2246
View this thread: http://www.excelforum.com/showthread.php?threadid=37889

Hi Mike,

you could enter this code in the sheet module where you would enter th
month in cell B1

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$1" Then
Range("A1") = "='C:\data\worksheets\team\[quality Scores.xls]"
Target.Value & "'!B2"
End If
End Sub


Manges

--
mangesh_yada
-----------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...fo&userid=1047
View this thread: http://www.excelforum.com/showthread.php?threadid=37889

And if you want a combobox instead, then select a combobox from th
control toolbox (View Toolsbars. select the control toolbox) and no
the forms menu. The name of this combobox would be ComboBox1. The
enter the following code in the module of the concerned sheet.


Private Sub Worksheet_Activate()
ComboBox1.LinkedCell = "Sheet1!B1"
ComboBox1.ListFillRange = "Sheet1!J1:J12"
End Sub

Private Sub ComboBox1_Change()
Range("A1") = "='D:\[quality Scores.xls]" & ComboBox1.Value
"'!B2"
End Sub


Manges

--
mangesh_yada
-----------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...fo&userid=1047
View this thread: http://www.excelforum.com/showthread.php?threadid=37889