Selected cell value in different cell?

[rooperi]

Hi,
I'm new here, forgive me if I'm posting in the wrong place...
I need a function or formula that places the value of the currently selected cell in a different cell. (say for eg F1). In other words, if A5 is the active cell, I would like the value of A5 to appear in F1.
Years ago, whe I still used Quattro, it was possible with @CELLPOINTER("contents"), is something similar possible in Excel?

 

[alow]

Do you want F1 to show any cell that's selected? If you're looking for it to show something that matches criteria or something, you could do that with a formula, depending on what you're looking for. Or you could do =A5 if you wanted it to show that. There may be a way to do what you want with VBA, but I think it may be more trouble than it's worth.

 

[rooperi]

Do you want F1 to show any cell that's selected? If you're looking for it to show something that matches criteria or something, you could do that with a formula, depending on what you're looking for. Or you could do =A5 if you wanted it to show that. There may be a way to do what you want with VBA, but I think it may be more trouble than it's worth.

Yes, I would like it to show any cell currently selected, so, if a5 then f1 should show the a5 value, if b7, then it should show the b7 value, etc
Maybe there is a way to make a dynamic range, where the range is the current selection? [EDIT] The idea being that F1 can then get the value from the dynamic range, just for clarification.

 

[alow]

This link from over at MrExcel should do it for you.

 

[rooperi]

Thank you very much, works like a charm

 

[pjmorris]

The only disadvantage with that solution is that to change the position of the 'output' cell you need to edit the macro, and the selected cell output can't be used as part of an equation as the macro over-rights the contents. The two pieces of code below should overcome that issue, and do, provided all the cells get recalculated.

It is the 'Calculate' code which is causing the problem. Sometimes the code works great, the SelectionChange macro stores the new value in 'CellValue' and the 'Calulate' forces the 'CellPointer()' function to be calculated and hence update the cell in which it is located. I've tried using the Range.Dirty and Range.Calculate methods to force a recalculation, but it still doesn't work reliably. If anyone else has a bright idea I'd be grateful.

The code I'm using is:

In the worksheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
CellValue = Target.Value
Range("I17").Dirty 'note Cell I17 was the cell in which I put the CellPointer Function
Range("I17").Calculate 'note Cell I17 was the cell in which I put the CellPointer Function

End Sub

In Module 1:

Public CellValue
Function CellPointer(t As Range)
If IsError(CellValue) Then
CellPointer = "Hmmm a Problem"
ElseIf CellValue = "" Then
Else
CellPointer = CellValue
End If
End Function


I hate code that nearly works!! Its just so frustrating.

All the best

Peter

 

[pjmorris]

Fixed it.

The attached file (for Excel 2010) has the code attached, just save it somewhere and delete (yes, I mean just delete) the '.zip' extension. The code is as follows:

In the code associated with Sheet1:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

On Error Resume Next
cellvalue = Target
Range("A1:G11").Dirty
​

End Sub
​

The Range.Dirty method should be changed to make sure it includes the cells that contain the ActiveCellValue() function.

In Module1:

Public cellvalue 'Provides storage for the value of the cell under the cursor

Function ActiveCellContent()

ActiveCellContent = cellvalue
​

End Function
​

This no means that the value at the active cell can be used as part of an equation not merely repeated elsewhere.

Hope this helps.

Regards

Peter