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-

Chad Borman wrote:
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.