Validation in Excel Sheet

H

harshaputhraya

Hi,
I have an EXcel sheet, where in i have 2 spread sheets.The first one i
display sheet where i want to get the values displayed from the secon
one which has all the data.

Example:
When the user enters an EMPLOYEE NO, the Excel sheet has to search fo
the EMPLOYEE NO stored as a list in the second spread sheet, and i
found display all the details say his address,phone no etc.If not foun
it should display "NOT AVAILABLE"

So i need t write a formula, where in it validates the EMPLOYEE NO i
the first spread sheet with that of the second spread sheet(which has
long list of EMPLOYEE NO's).

How do I do that.Please let me kno
 
P

Pete_UK

Define a named range in the second sheet which covers all the Employee
Numbers - highlight all the cells, eg A2 to A100, then Insert | Name |
Define and give this a single name, such as "Staff".

Then in your first sheet select the cell where you want to enter the
Employee Number and click Data | Validation then Settings (tab) and in
the Allow panel select List and in the Source panel enter =Staff. You
can also specify on the other tabs an input message if you want one to
appear, and also specify what you want to do if an invalid entry is
made in the cell, i.e. "Not Recognised". Then click OK.

Now when you click on the cell where you want to enter the Employee
Number you will see a pull-down, from which you can only select the
numbers on your second sheet.

In other columns you can use a VLOOKUP( ) formula to bring across the
other data that you require, such as address, phone number etc.

Hope this helps.

Pete
 
Top