VLookup and Data Validation

  • Thread starter Thread starter luv2waterski
  • Start date Start date
L

luv2waterski

I have VLookup & Data Validation working beautifully. My question
however is the following: After I have a value pulled using VLookup,
I want to be able to delete the formula but keep the value. Is there
an easy way of doing this for a range of cells that contain the
VLookup formulas? Thanks.

-Darren
 
What you do is copy it to itself, telling it to copy only the values.

Select the cells that contain the formulas that you wish to eliminate and
Right click in the selection and choose "Copy".

Right click in the selection again and choose "Paste Special".

Click on "Values", then <OK>.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

I have VLookup & Data Validation working beautifully. My question
however is the following: After I have a value pulled using VLookup,
I want to be able to delete the formula but keep the value. Is there
an easy way of doing this for a range of cells that contain the
VLookup formulas? Thanks.

-Darren
 
Thanks to all who answered, I was just coming back on to reply to my
own question with the answer that I came up with and guess what. My
esteemed newgroup helpers put down the same exact solution. Thank you
all.

-Darren
 
xl2002.
I had a similar problem where I wanted a worksheet_change event to create
the value of the lookup formula. I found that it would not work properly so
I just put =now() on a cell on the worksheet and used the
worksheet_calculate event.

'where mylookup is a defined name for the lookup range
Private Sub Worksheet_Calculate()
Application.EnableEvents = False
On Error GoTo quitit
'=====
If ActiveCell.Column = 1 And ActiveCell.Row >1 Then
ActiveCell.Offset(, 1) = _
Application.VLookup(ActiveCell, [mylookup], 2, 0)
'========
end if
quitit:
Application.EnableEvents = True
End Sub
 
Darren

Select the cells to alter.

Edit>Copy. With the cells still selected Edit>Paste Special>Values>OK

Gord Dibben XL2002
 
Back
Top