If / Vlookup display result not formula

Hi all,

Hopefully you will be able to help again. Is it possible to do a vlookup that references data on other tabs within the worksheet so that the result of the formula is in the cell not the formula.

So if my vlookup was =vlookup(a2,$a$1:$b$12,3,0) and the result was john smith i want just john smith in the cell. I know about copy and paste values but i was looking for a more automatic way. One that doesn't need intervention.

Thanks for your help.

Are you saying you are seeing the formula and not the result in the cell?

I think I understand, you just want the result to appear without using a formula, so you want to use VBA to do this. Is this correct?

Originally Posted by Chemistification

Im pretty weak on vba but if thats what it takes yes please. It shows the result in the cell which is fine but in the formula bar shows the formula behind the result. I would like it just to show the persons name ect. Thanks for your time

You can if you use VBA of course, to get the result, or you can turn off the formula bar, check this out, http://excel.tips.net/Pages/T002729_...rmula_Bar.html.

Or you can hide the cell formula. This would require protecting your sheet though.
Right click on the cell and select format cell, go to the protection tab and check off "hidden", then protect the sheet. The formula will not show in the formula bar.

Other preparations would have to be done to make your sheet workable though. Like unlocking all the cells that require data entry before you protect the sheet.

Originally Posted by Chemistification

Thanks for the reply dave. THis is for a boss at work and i told him the bit about hiding formulas but he said he still needed the name in the cell not the formula...
Got any good links to the vba code needed? Im ok at inserting the code into a module tho

The name would still be in the cell, nothing would appear in the formula bar.

For the VBA Option, a link would not be required, attach a small sample workbook, so a code can be written,
Or show what the real Vlookup Formula would be, the one in your first post seems a little off.

I am sorry I have run out of time, here is an equivilant to your vlookup formula, using vba

Please Login or Register  to view this content.

Check out worksheet_change events

The word line manager actually has a space at the end.

This seems to work, select the line manager name, the formulas will change to values showing the value in the formula box instead of the formula itself.
It uses Worksheet_Change event