MrExcel Publishing
Your One Stop for Excel Tips & Solutions

look up tables - help?


Posted by daveu on August 22, 2001 12:30 PM

Is there a way attaching a look up table or list to selected cells within a sheet. Much in the same way as you can view and select from a list using 'pick from list'. In this case however I want the list to be on a separate sheet and to be able to select an entry from it. What I'm trying to do is constrain or force the data enterer to select a value from a specified list.
thanx


Posted by lenze on August 22, 2001 12:57 PM

Daveu: Data>Validation. Use the list option with dropdown box

Posted by daveu on August 23, 2001 2:16 AM

validation - ref. to other sheets not allowed

Hi Lenze - thanks for the advice - you've got me on the right track. However - the validation function doesn't allow references to other worksheets or workbooks (states this in error message). Id there another way round it that you can think of?

Posted by lenze on August 23, 2001 6:27 AM

Re: validation - ref. to other sheets not allowed

Posted by lenze on August 23, 2001 6:33 AM

Re: validation - ref. to other sheets not allowed

Daveu: Instead of using cell references, use a named range. After you enter your list, select it and choose Insert>Name>Define and give it a name. Then, in the validation window enter "=yourname" instead of a cell reference. This will work across different sheets in the workbook.

Another way is to put the list on the same sheet off to the side or below the form and then hide the rows or columns

Posted by daveu on August 23, 2001 11:38 PM

thanks mate - you're a star