Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
combine Vlookup with the Right function | Excel Worksheet Functions | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
Possible to combine VLOOKUP and IF(AND) functions? | Excel Worksheet Functions | |||
How do I combine If and VLookup function? | Excel Worksheet Functions | |||
How to combine a vlookup with a sumif function!!! | Excel Discussion (Misc queries) |