MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excell Data

Posted by Keith on November 22, 2001 7:14 PM

I am trying to use data validation (list) but it will not let me copy information from one worksheet and put it into another. I have made up several forms for use at work and I want to use one worksheet for information, hide the sheet and use the other worksheet as my form with data information in the cells.

Posted by Tom Urtis on November 22, 2001 10:57 PM

If what you are asking is how to validate a cell in one worksheet with a list of values that is located on another worksheet, what you need to do is highlight the list of values, and click Insert > Names > Define, then type in your range name in the "Names in workbook" field and click OK. Then you can hide that sheet. Now go back to the sheet where the validated cell is located, and click Data > Validation > Settings tab. In the "Allow" field choose List, and in the "Source" field enter =MyNamedRange, where MyNamedRange is whatever you named your list range on the hidden worksheet. Click OK and you are good to go.

Any help?

Tom Urtis