=INDIRECT to return intersection of ActiveCell row and specific Column

[2Took]

On Worksheet_SelectionChange in L1 I have VBA populate ActiveCell.Address

Range("L1").Value = ActiveCell.Address

Trying to figure out a formula, by using value in L1, to return intersection of ActiveCell Row and specific Column.

I believe =INDIRECT formula might be a way to get it done, something along the lines of =INDIRECT(L1&"H"), but that's not quite it, gives #REF!

L1 shows references this way: $G$11
In that example 11 is the ActiveCell Row, guessing needs to be extracted and "H" added to it...

 

[2Took]

To clarify, I need to have contents/value of resulting cell address returned, e.g. if cell H11 value is "ABC123" I want to see that showing in a cell where the formula I am trying to figure out is going to be at.

I got formula below so far, which combines H and the ActiveCell Row reference stripped from L1, which returns everything after the last occurrence of "$" in L1.

="H" & RIGHT(L1,LEN(L1)-SEARCH("#",SUBSTITUTE(L1,"$","#",LEN(L1)-LEN(SUBSTITUTE(L1,"$","")))))

But, result I am seeing in a cell with this formula is literally "H11", whereas I'd like to see the contents/value of H11, which is "ABC123"

 

[2Took]

I knew I was almost there

Solution is to wrap it in =INDIRECT("H" & RIGHT(L1,LEN(L1)-SEARCH("#",SUBSTITUTE(L1,"$","#",LEN(L1)-LEN(SUBSTITUTE(L1,"$",""))))))

 

[Peter_SSs]

Whilst that will work ...

1. Why not have the worksheet change code directly put the result in whatever cell you have this volatile function formula in?
   
   
2. If you really want to use a formula, what about this much simpler one
   

   =INDIRECT("H"&TEXTAFTER(L1,"$",-1))

   
   
3. Or if the user(s) do not have the TEXTAFTER function, one of these
   

   =INDIRECT("H"&REPLACE(L1,1,FIND("$",L1,2),""))

   =INDIRECT("H"&MID(L1,FIND("$",L1,2)+1,7))

 

[2Took]

Whilst that will work ...

1. Why not have the worksheet change code directly put the result in whatever cell you have this volatile function formula in?
   
   
2. If you really want to use a formula, what about this much simpler one
   

   =INDIRECT("H"&TEXTAFTER(L1,"$",-1))

   
   
3. Or if the user(s) do not have the TEXTAFTER function, one of these
   

   =INDIRECT("H"&REPLACE(L1,1,FIND("$",L1,2),""))

   =INDIRECT("H"&MID(L1,FIND("$",L1,2)+1,7))

Thanks,

Simplest one is actually

=ROW(INDIRECT(L1))

 

[Peter_SSs]

Simplest one is actually

=ROW(INDIRECT(L1))

That of course is only part of the final formula, and I'm not sure that those two function calls to get the row number are simpler than the single function ..

TEXTAFTER(L1,"$",-1)

Simpler again would be to have your selection change code as

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Range("L1").Value = "H" & ActiveCell.Row
End Sub

Then all you would need is

=INDIRECT(L1)

.. or if you might be referencing several different columns with different INDIRECT functions then

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Range("L1").Value = ActiveCell.Row
End Sub

=INDIRECT("H"&L1)+INDIRECT("X"&L1)

 

[2Took]

First one uses unavailable formula.
Second would require to go to VBA to adjust column.
The latter one is the most streamlined, with the exception of formula does not take "+", takes "&"

Thank you, I always learn from you

 

[Peter_SSs]

with the exception of formula does not take "+", takes "&"

That was just an example and depends on the data in the columns. The formula works fine with "+" if the data in the two columns is numerical. Since we didn't know if you might want to reference two (or more) columns and we have no idea of what your data is, I took a guess to demonstrate the principle.

For the sheet below my selection change uses the last one in my previous post to return only the active cell row to L1.
Formula in M1 is =INDIRECT("H"&L1)+INDIRECT("X"&L1) and you can see with the active cell in P6 the M1 formula works.