UDF to retrieve formula in target cell

[Madhav]

Hi,

I need to create a custom VBA function to perform an operation similar to VLOOKUP.

I want to perform a search operation like VLOOKUP. But the only difference is, I need to retrieve the formula in the target cell instead of retrieving value in the cell, and I don't want to print the retrieved formula in the cell (like FORMULATEXT function), instead I want to paste it like 'paste special -> Formulas'.

Is this possible?

Or apart from creating UDF, is there any other way to do this?

 

[Joe4]

Welcome to the Board!

Please post some specifics so that we can help you, i.e. a sampling of your data, where we can see that columns and rows everything is in, and where you want to return these results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Madhav]

Welcome to the Board!

Please post some specifics so that we can help you, i.e. a sampling of your data, where we can see that columns and rows everything is in, and where you want to return these results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Hello,

Please find the attached image. The requirement is highlighted in green.

The expected solution is also mentioned in the screenshot. (Any other methods to solve this are also welcomed).

I'm not able to use XL2BB. So I'm attaching only screenshot.

 

[Joe4]

I am not sure what value column H plays, since it will always be “Pass” and it appears you are manually writing the formula dependent upon what is in column G. It seems to me that it is totally unnecessary (it is not telling you anything that you cannot already deduce from column G).

Why not just do a VLOOKUP in column C like this (for cell C3):

=IF(B3>=LEFT(VLOOKUP(A3,$F$3:$G$22,2,0),1),"Pass","Fail")

and copy down for all rows in column C?

 

[Madhav]

=IF(B3>=LEFT(VLOOKUP(A3,$F$3:$G$22,2,0),1),"Pass","Fail")

With this formula, I'm able to get the expected result.

Thank you.

 

[Joe4]

You are welcome.
Glad I was able to help!