choosing cells from another sheet

[Guest]

The best way I know of to ask is to give an example. I have two sheets, the
first sheet has two columns, column A has item numbers, column B has item
descriptions. On second sheet, I want to create a drop down box so when I
type in or choose an item description (that matches a description from sheet
one), that item description populates column B of sheet two, AND, the
matching item number is automatically put into column A of sheet two..
TIA

 

[Guest]

The best way I know of to ask is to give an example. I have two sheets, the
first sheet has two columns, column A has item numbers, column B has item
descriptions. On second sheet, I want to create a drop down box so when I
type in or choose an item description (that matches a description from sheet
one), that item description populates column B of sheet two, AND, the
matching item number is automatically put into column A of sheet two..

One way ..

A sample construct is available at:
http://www.savefile.com/files/3288974
Dynamic range DV n Index n Match.xls

Assume source data is in Sheet1, cols A & B, data from row 2 down
Item# in col A, Item Descriptions in col B

In Sheet2,

Create a dynamic range for use in the DV droplist for col B
Click Insert > Name > Define
Names in workbook: ItemDesc
Refers to: =OFFSET(Sheet1!$B$2,,,COUNTA(Sheet1!$B:$B))
Click OK

Set up the DV in col B:
Select B2:B10 (say)
Click Data > Validation
Allow: List
Source: =ItemDesc
Click OK

Then just place in A2:
=IF(B2="","",INDEX(Sheet1!A:A,MATCH(B2,Sheet1!B:B,0)))
Copy down to A10

Col A will return the Item# for the Item description selected in col B

---

 

[Guest]

Correction to line:

Refers to: =OFFSET(Sheet1!$B$2,,,COUNTA(Sheet1!$B:$B))

should read as:
Refers to: =OFFSET(Sheet1!$B$2,,,COUNTA(Sheet1!$B:$B)-1)

(Sample corrected as well)

---