Application Question

[Rich Wojcicki]

How can I have a fixed cell on sheet2 equal the active
cell on sheet1 is there a function or code I can use.

Thanks, Rich

 

[Guest]

Yes.

in cell B2 on sheet 2 enter:

=Sheet1!A1

Now the contents of Sheet1 cell A1 will always appear in
Sheet2 Cell B2

 

[Don Guillett]

a macro
activecell=sheets("sheet2").range("a1")
formula
in any cell =sheet2!$a$1

 

[marginaluser]

use the basic reference formula. for example: sheet 1 is called apples and sheet two is oranges. you want cell A2 in oranges to equal whatever is in cell B76 in apples. in oranges A2 type =apples!B7

best of luck

 

[Jack Schitt]

How can I have a fixed cell on sheet2 equal the active
cell on sheet1 is there a function or code I can use.

Thanks, Rich

Hi Rich, my solution is a bit kludgy, but:

Into the code behind the worksheet "Sheet1" enter

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ThisWorkbook.Worksheets("Sheet2").Names("Sheet1Active").Value _
= "=Sheet1!" & Target.Address
End Sub 'Worksheet_SelectionChange(ByVal Target As Range)

Then create a locally defined named range in Worksheet "Sheet2":
Sheet2!Sheet1Active
It doesn't matter what you initially assign it to refer to, but say
=Sheet1!$A$1

Then in your fixed cell, say Sheet2!B5 enter a formula
=Sheet2!Sheet1Active

Then go back to sheet 1 and change the activecell, and observe the effect on
Sheet2!B5

Problem with the solution as it stands is that it will return numerical
value "0" when the activecell in Sheet1 is empty, which may not be entirely
desired. There is probably a way around that.

JS

 

[Jack Schitt]

Oops, slight improvement:

For the code in the module behind Sheet1 replace with the following:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Application.ThisWorkbook
.Worksheets("Sheet2").Names("Sheet1Active").Value = _
"=Sheet1!" & ActiveCell.Address
End With 'Application.ThisWorkbook
End Sub 'Worksheet_SelectionChange(ByVal Target As Range)

Reason: Target may be a range of multiple cells. You only wanted the
ActiveCell.