Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
choosing cells from another sheet
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
choosing cells from another sheet
"RPW" wrote:
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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
choosing cells from another sheet
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) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i fill down a series of linked cells | Excel Discussion (Misc queries) | |||
Replacing sheet reference in multiple cells... | Excel Worksheet Functions | |||
checking that cells have a value before the workbook will close | Excel Worksheet Functions | |||
Formula to copy rng of cells where (value is met) to anther sheet | Excel Worksheet Functions | |||
Entering Data in multiple cells on one sheet & having it auto upda | Excel Worksheet Functions |