active cell

[charly1]

hi all

I am relatively new to excel, so my question may indeed be a simple one.

I would like to write a "xlookup formula" in which the "lookup value" is constantly updated to refer to the "activecell". Is this possible without using vba and without having to double click the active cell to update the reference in the cell containing the xlookup formula?

Any help would be greatly appreciated!

 

[charly1]

Sorry, there is a typo in that code. When I was trying to copy the special characters of your alphabet, I lost an equal sign. It should be:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'   Check to see if selected cell is in column P under row 3
    If Target.Column = 16 And Target.Row > 3 Then
'       Update formula in cell A1 with address of selected cell
        Range("A1").Formula = "=XLOOKUP(" & Target.Address & ",ArtistName,Table2[[עמודה2]:[עמודה7]])"
    End If
   
End Sub

Thanks again, and IME the one that sorry for driving you crazy. It still doesn't work though, although I think I've worked out the reason.

After selecting a cell in row P, the vba runs and Cell A1 becomes populated with ####, the formula written in that cell is =@XLOOKUP($P$4,ArtistName,Table2[[column2]:[column7]]), it seems that for some reason @ is getting inserted after the = and causing the issues.
Am I right?

(BTW I exchanged the header titles in the table from special characters to just coloum2 and 7 respectively).

 

[Joe4]

Try this change and see if that fixes it:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'   Check to see if selected cell is in column P under row 3
    If Target.Column = 16 And Target.Row > 3 Then
'       Update formula in cell A1 with address of selected cell
        Range("A1").Formula2 = "=XLOOKUP(" & Target.Address & ",ArtistName,Table2[[עמודה2]:[עמודה7]])"
    End If
   
End Sub

Note we just changed "Formula" to "Formula2".

 

[charly1]

Try this change and see if that fixes it:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'   Check to see if selected cell is in column P under row 3
    If Target.Column = 16 And Target.Row > 3 Then
'       Update formula in cell A1 with address of selected cell
        Range("A1").Formula2 = "=XLOOKUP(" & Target.Address & ",ArtistName,Table2[[עמודה2]:[עמודה7]])"
    End If
  
End Sub

Note we just changed "Formula" to "Formula2".

Just checked it, and holy cow it works perfectly!! This is going to be extremely useful for me! Thanks so much for your time and effort!

 

[Joe4]

You are welcome.
Glad I was able to help!