Change a Cell Ref When Clicking in another Cell

[Guest]

I'd like to be able to change a cell reference in one cell based upon which
cell I've currently clicked in. Example. My formulas are all entered in
Sheet1. If I click in cell B1 on Sheet1, the value in cell A1 on
Sheet1refers to Sheet2!A1. If instead, I click in cell C1 on Sheet1, the
value in A1 on Sheet1 will refer to Sheet3!A1 and so on. I'm only somewhat
fluent in VBA. Any help would be appreciated.

 

[JW]

In the Microsoft Excel Objects folder in your VBA Project, click on
Sheet1.
Using your example below, add this into Sheet1's module.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
if Target.Address = "$B$1" Then
Cells(1,1).Formula = "=Sheet2!A1"
ElseIf Target.Address = "$C$1" Then
Cells(1,1).Formula = "=Sheet3!A1"
End If
End Sub

Simply extend this same pattern for however many different scenarios
you have.

HTH
-Jeff-

 

[Guest]

Thanks for your help, JW. It worked (with one minor change - I had to
enclose the sheet names between single quotes).

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
if Target.Address = "$B$1" Then
Cells(1,1).Formula = "='Sheet2'!A1"
ElseIf Target.Address = "$C$1" Then
Cells(1,1).Formula = "='Sheet3'!A1"
End If
End Sub