Change a Cell Ref When Clicking in another Cell
 

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.

Change a Cell Ref When Clicking in another Cell
 

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.

Change a Cell Ref When Clicking in another Cell
 

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

"JW" wrote:

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.