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 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

 

[mangesh_yadav]

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_yadav]

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