Link Problem Can a MAcro do this?

[Mikeice]

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 will dynamically change the Worksheet name to whatever month is selected in 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 workbook with all the current data.

[mangesh_yadav]

Hi Mike,

you could enter this code in the sheet module where you would enter the 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


Mangesh

[mangesh_yadav]

And if you want a combobox instead, then select a combobox from the control toolbox (View > Toolsbars. select the control toolbox) and not the forms menu. The name of this combobox would be ComboBox1. Then 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


Mangesh