combine vlookup and copy function
 

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?

combine vlookup and copy function
 

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,V LOOKUP(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!


"pamber" wrote:

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?

combine vlookup and copy function
 

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.

"andy62" wrote:

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,V LOOKUP(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!


"pamber" wrote:

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?