combine vlookup and copy function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

pamber - 15 Sep 2007 00:30 GMT

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?

Reply to this Message

andy62 - 17 Sep 2007 18:50 GMT

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!

> 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.
[quoted text clipped - 7 lines]
>
> Can this be done?

Reply to this Message

pamber - 17 Sep 2007 23:12 GMT

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.

> 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
[quoted text clipped - 22 lines]
> >
> > Can this be done?

Reply to this Message