Populating worksheet via a drop down list !

[kuansheng]

I'm working on creating worksheet selection from a drop down
list. The drop down list contain: Mouse, Keyboard, Monitor for example:


I have 2 worksheet in the same workbook.
I have a worksheet called (BOM), one of this worksheet for Mouse,
another for Keyboard and so on. that contain data in 4 column namely:
Model, Part number, Part description and Quantity and has about 100
rows

For example if i choose "Mouse" from the drop down list then I want
cell A5 to fill with "Model number" and cell B5 with "Part number." and
cell A6 with "Part description." and cell B6 with "Quantity"

All of this fill-in information would be on another worksheet named
Master plan(MP).

I am using formula that refers to data data on the BOM worksheets like
this: ='BOM'!A2 in cellA5 of MP, and ='BOM'!B2 in B5 of MP, ='BOM'!C2
in A6 of MP and so on. And i repeat by copying the formulas to cell
below to extract the data for row 2 of the BOM worksheet. I continue
until i get all 100 rows.

My problem is when i copy this formula to some cell below, it does not
extract the next row of data from the BOM worksheet. Instead it make
relative reference to the cell thus returning wrong data.

Is there a way of going about to do this. Any help is appriciated.
Thank you.

For example i choose from the drop-down list: mouse

BOM worksheetcorresponding to mouse)
Model Part number Part Des Quantity
M123 02-1234-12 Screw 1
M124 02-1235-12 Label 2

The data will be fill-in to the respective cell in the MP worksheet
automatically

MP worksheet:
Model:Fill-in here Part number: Fill-in here
Part Des:Fill-in here Quantity:Fill-in here

 

[Peo Sjoblom]

Look if you can use data>validation with vlookup

http://www.contextures.com/xlFunctions02.html

http://www.contextures.com/xlDataVal01.html

--
Regards,

Peo Sjoblom

Northwest Excel Solutions

Portland, Oregon

 

[kuansheng]

How can i use vlookup to reference data on another sheet.

 

[Peo Sjoblom]

Did you mean validation on another sheet, if so use a defined name for your
list
if you meant vlookup refer to the other sheet

=vlookup(lookup_value,'other_sheet'!$A$2:$E$50,3,0)

will lookup lookup_value in other_sheet A2:A50 and return value from C2:C50

--
Regards,

Peo Sjoblom

Northwest Excel Solutions

Portland, Oregon

 

[kuansheng]

Thank for the advice Peo. I have try to use Scenario in excel
apparently there is a limitation of only 32 changing cell is allow. Peo
do youhave any idea how to go about this limitation. Thanks in advance