Displaying description, when code is entered

[FarzanaSaleem]

There are two sheets in an Excel Workbook; Sheet1 and Sheet2.

Sheet1, Column A:
A1 = Job Titles
A2 = MGR
A3 = ACT
A4 = PROG

Sheet1, Column B:
B1 = Description
B2 = Manager
B3 = Accountant
B4 = Programmer

JobTitles is a named range and it refers to:
=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,1)

Sheet2, Column A:
A1 = Job Titles
A2 = Data -> Validation -> List (Source = JobTitles)

Sheet2, Column B:
B1 = Description

What is needed is whenever any Job Title Code is selected in Column A->Sheet2, its corresponding description needs to be populated/displayed automatically in Column B->Sheet2.

For example, if MGR in selected in A2, value of B2 is needed to be populated/displayed as Manager.

 

[mrsnrub]

Sounds like you need a VLookup!

Try this. Change your JobTitles range, or create a new named range, equal to:
=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,2)

Then, in cell B2 of Sheet 2 (using your example) enter the formula:
=VLOOKUP(A2,JobTitles,2,FALSE)

This will search for the value defined in cell A2 (ie. MGR) through the range 'JobTitles' and return column 2 (ie. the description column) when a match is found. If you want further info, do a search for Vlookup in the Excel help files.

Hope this helps.

 

[FarzanaSaleem]

Thanks.

VLOOKUP solved the issue. The only problem, I am facing is that when I changed JobTitles range from =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,1) to =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,2), drop-down combo in A2 (Sheet2) showed job titles and description both:
MGR
Manager
ACT
Accountant
PROG
Programmer

Hence I had to define a new range JobTitlesDesc and used that range in VLOOKUP forumula.

I have many tables defined in Sheet1, like JobTitles, Departments etc. I will have to define a new range for each table to show description.