using user vlookup to return a formula where the row number component is always the current row

[AndrewI]

Hi everyone. Thanks in advance for reading this.
I'm trying to use VLOOKUP to return formula which it retreives from a table on a different tab. When the VLOOKUP is row 10, the formula would be (AB10/P10)*1000.
The difficulty i'm having is that when the row the VLOOKUP changes, the rows in the formula should match the current row, so in row 11 it would be (AB11/P11)*1000.

Now obviously i can't build the lookup table to account for every row so wondering how I'd construct that formula?
I'm then using the UDF "EVALUATETXT" (see below) to evaluate that text as if it were a formula. I keep getting #VALUE and #REF errors with every method I try.

UDF code:
Function EVALUATETXT(s As String) As Variant
Application.Volatile
EVALUATETXT = Application.EVALUATE(s)
End Function


Thanks

 

[AndrewI]

this is as close as I can get (changed the column refs as I am testing on the same tab as the table so i can show you guys .
The result in the focused cell should be 3.33333 and not the formula text

Thanks

 

[AndrewI]

sorry.. also, if i "EVALUATETXT" that result in a different cell i get the right answer, but if I try to nest it all in one cell ie (EVALUATETXT(EVALUATETXT(ref) i get a #value error

 

[AndrewI]

Hi everyone. Thanks in advance for reading this.
I'm trying to use VLOOKUP to return formula which it retreives from a table on a different tab. When the VLOOKUP is row 10, the formula would be (AB10/P10)*1000.
The difficulty i'm having is that when the row the VLOOKUP changes, the rows in the formula should match the current row, so in row 11 it would be (AB11/P11)*1000.

Now obviously i can't build the lookup table to account for every row so wondering how I'd construct that formula?
I'm then using the UDF "EVALUATETXT" (see below) to evaluate that text as if it were a formula. I keep getting #VALUE and #REF errors with every method I try.

UDF code:
Function EVALUATETXT(s As String) As Variant
Application.Volatile
EVALUATETXT = Application.EVALUATE(s)
End Function


Thanks

Any help warmly received