Updating cell contents via VLOOKUP ???

[jpricesr]

Background - Three worksheets (COVER, LOOKUP and LOG) are in sam
workbook (file).

COVER worksheet has area (in column format) that I put in info and
marco inserts a row, copies and pastes this info onto the LOG shee
(transposed into 1 row per record (This is used to log Machinica
trouble tickets , so it has Ticket number, date, Reported by, Location
issue, resolved (Yes or No)... then cleans out the info so I can type i
another ticket.

LOOKUP worksheet has VLOOKUP formulas so in the cells to retrieve th
data based on the what I type in cell D10 (Ticket number).. this work
fine

LOG worksheet stores the data that I have inputed from the COVER pag
and is the source for the LOOKUP sheet for the VLOOKUP formulas...

MY PROBLEM - we don't have ACCESS and this would be easy for me if w
did BUT,
I want to be able to UPDATE the info in a record (row) after I retrive
it based on Ticket number.. but obviously I can't type over the VLOOKU
formulas to update so how can I do this within this structure????

I would like to be able to do this within the LOOKUP sheet if possibl
(maybe hide the results of the VLOOKUP on the side and somehow link th
cells retrived by VLOOKUP to a visable part of the sheet and maybe
macro to UPDATE????

Add a worksheet ???? that does this???

PLEASE HELP

Jack Price
Uta

 

[Frank Kabel]

H
not really possible as you can't have a formula and a value in the same
field. Only possible with some more complicated VBA code (using event
procedures)

 

[jeff]

Hi,

Here's some code to get you started on using a macro
to set the values rather than the Vlookups. each time
the worksheet changes, it fires. Expand, clean up, etc
as needed. Put in worksheet module.

Private Sub Worksheet_Change(ByVal Target As Range)
if intersect(target,me.range("$C$4:$C$4")) is nothing
then exit sub
If Target.Address = "$C$4" Then
Range("C3").Value =
Application.WorksheetFunction.VLookup(Range("C4"), Range
("G1:H3"), 2)
End If
End Sub


jeff