04-26-2004, 04:18 PM
I use Data Validation all the time. There are a few tricks you should be aware of:
Insert Name Define
Range names allow you to tuck your drop down list away on a background sheet or even in a separate workbook. Cell references only work for the active sheet.
Use MATCH and INDEX rather than VLOOKUP, HLOOKUP or LOOKUP
There's a memory leak on the LOOKUPs and you're restricted to looking up on the first column of a table in any case. It's much safer to do a MATCH to find a cross-reference then use the result to pull out the appropriate response. You don't need to remember column numbers for a start.
Insert Name Define
Range names allow you to tuck your drop down list away on a background sheet or even in a separate workbook. Cell references only work for the active sheet.
Use MATCH and INDEX rather than VLOOKUP, HLOOKUP or LOOKUP
There's a memory leak on the LOOKUPs and you're restricted to looking up on the first column of a table in any case. It's much safer to do a MATCH to find a cross-reference then use the result to pull out the appropriate response. You don't need to remember column numbers for a start.