combine vlookup and copy function

[Guest]

Is is possible to combine a copy and vlookup function? I've created a
worksheet for others where information can be selected by drop down lists.
Whatever they select will then call up addtional information via a vlookup.
But, in case the information they are looking for is not already in the list,
they need to be able to type it in - so I cannot protect those cells and do
not want to lose the formula for future use.

If I could put the formula somewhere else and include a 'copy to' command,
it would solve the problem.

Can this be done?

 

[Guest]

I'm sure you could do it through some event macro, but another way might be
to use a third column where the user types the additional data needed when
their first piece of data was not in the drop-down list. So if they typed
the data in column A, rather than selected from the drop-down, then the
lookup formula (protected) in column B would pull in the second piece of
user-entered data from column C instead of using the VLOOKUP. Something like
this:

=IF(ISERROR(VLOOKUP(A2,Sheet2!A1:B9,2,FALSE)),C2,VLOOKUP(A2,Sheet2!A1:B9,2,FALSE))

If the VLOOKLUP returns an error, then the IF statement redirects to pull
the second piece of data from column C where the user entered it.

Hope that helps - or gets your question back into circulation!

 

[Guest]

Thank you.

While not completely what I need, that formula works for two of the three
issues I had. The one it does not work for is a cell that contains a drop
down list which can also contain a formula, but cannot be protected, so I'm
still one cell short.