Display cell reference value

[Benjaminmin]

Hi,

I have a sheet with a lot of prices that relates to a specific date

The population of the prices table all have formulas that look like this:
Cell Z15: =VLOOKUP(F15,$A$5:$B$10001,2,0)

Now, my question is, can I make a formula in say cell Z1, that displays the value that the cell I am currently in refers to. So that if I was in Z15, it would display the value of F15? (and if I was in Z1050, it would display F1050)?

Thanks in advance

 

[Lewiy]

Not exactly sure what you want, but you can use the Trace Precedents feature to see which cells are referred to in a formula.

 

[Benjaminmin]

Not exactly sure what you want, but you can use the Trace Precedents feature to see which cells are referred to in a formula.

Hi there,

No I wanted it to display the date that is in the referred cell, its because its a massive sheet, so when navigating through the columns of prices, it would be useful to see which date your current cell represents.

 

[Lewiy]

In that case, surely you just want =F15

 

[Benjaminmin]

In that case, surely you just want =F15

Well that would be a static result, I am looking for something dynamic.

As I wrote in the OP I am looking for something displays the value that the cell I am currently in refers to. So that if I was in Z15, it would display the value of F15? (and if I was in Z1050, it would display F1050)?

Probably this has to be done in VBA somehow

 

[Lewiy]

Ok, in that case
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim str As String
    Dim Comma As Long
    
    Application.EnableEvents = False
    
    'On Error GoTo ErrorHandler
    
    If Target.Cells.Count = 1 Then
    
        If InStr(ActiveCell.Formula, "VLOOKUP") > 0 Then
            Comma = InStr(ActiveCell.Formula, ",")
            str = Mid(ActiveCell.Formula, 10, Comma - 10)
        End If
        
        Range("A1") = Range(str).Value
        
    End If
<o:p> </o:p>
ErrorHandler:
Application.EnableEvents = True
End Sub

 

[Benjaminmin]

Hi Lewiy,

And thank you for your time and help.

I must admit I am a big newbie when it comes to VBA,

What do I write to apply your code to cell AB1 for example?

Thank you

 

[Lewiy]

Just Change this line

Range("A1") = Range(str).Value

To be the destination cell that you want (i.e. change the A1 to AB1)

 

[Benjaminmin]

Just Change this line

Range("A1") = Range(str).Value

To be the destination cell that you want (i.e. change the A1 to AB1)

Hi again,

Just tried this now, but I get an error:
Run-Time Error '1004':
Method 'Range' of object'_Worksheet' failed.

Any suggestions?