how to have vlookup w/ optional data entry

[chief]

Is it possible to have a formual in a cell in excel vba that is

=IF(D22="","",VLOOKUP(D22,$N$1:$Q$1459,IF($L$1="trad
sale",3,4),FALSE))

but have some sort of code set up in vb to reimplement the formula i
the cell if the user needs to input data manually into that cell. I a
hoping to be able to have the formula do its thing when needed, and the
if they need to over-ride a price they type it into that formula cell.
Then once they save the sheet, close it and re-open it, the formul
will get put back into that cell to be used as a vlookup again. Am
day dreaming or can this be done

 

[Frank Kabel]

Hi
yes this can be done but I'd suggest not doing this. You'll overwrite
existing changes and the user won't recognize this. I don't think this
would be a good spreadsheet design. For such cases I would always use
two different cells. A formula claculated and an additional manual
entry filed.

I then would adapt my formulas to use the manual entered value if
available. This way you also have a kind of 'audit' trail