Pull down menues with automatic values

[kristoficus]

Well this is my first time here and the first time i have known abou
the forum.

Im not into Excel as a use on a daily base scenario, but i have got t
use it a lot in my University course from now on.

Ive been asked to produce a spreadsheet which will allow a user t
create their own computer. The user HAS to select certain component
like motherboard, hard drive, processor etc.

Beside each component there should be a drop down list to give the use
a wide choice of products relating to the component, then when the
select what they want, the price of that component should show u
beside the component they actually clicked on.

Im not sure, do i put the component products in a new sheet and lin
them somehow - i really have no clue, but i know most the basics.

I know this sounds very specific, but im really stuck with it and if
dont get it done, my final grade could possibly suffer.

I will be eternally gratefull to whomever is able to produce me with
few lines of info on how to acheive what is needed..

 

[Earl Kiosterud]

Kristoficus,

I'll give you an approach to use. I won't give you the details.

You'll need to make lists of each component (motherboards, a list of hard
drives, etc). In the list should be the price of each component in an
adjacent column. Then you can use Data validation - List in the cell(s) in
which the user will select a component -- you can specify a dropdown. Point
Data Validation to the column of the appropriate list containing the
component names. You'll use VLOOKUP in the cell where the price is to
appear, it will look up the component in the appropriate table (it will have
a reference to that component table), and get the price. The fourth
argument of VLOOKUP should be FALSE (just in case).

Note that if a component list is in another sheet, Data Validation can't use
it (unless you use range names). So to keep this simple, put the component
lists in the same sheet (though that's not particularly good worksheet
design, but I think it's fine for this project). You can hide the columns
or rows of the component tables if you want to.

 

[kristoficus]

Thank you very much.

Ill consider it as a sort of challenge then

I have all my components etc on a different sheet from the one the use
will select what he/she wants. All of them have range names - i.e, al
the motherboards are selected and have name 'motherboard' etc.

Is it possible for me to specify something as a default value, i.e.
"Please select a component"?

Im not familiar with VLOOKUP but theres always the office assistant.

Again, thanks for the help

 

[kristoficus]

Sorry but im now more confused that a 10 year old looking at quantu
physics.

I have all my lists and they work fine - click the arrow then selec
the thing you want. But this vlookup is just really confusing, i mean
how do i add this formula to display one of up to 6 prices (max of si
pruducts in any one component area).

I have 2 sheets in use - sheet one is where the user selects his/he
product and then the price is displayed beside it. Sheet 2 has all th
list data and the components are sorted with their group names.

Could you give me an example?
Please help me with a formula for this: Sheet 1, Cell B10 has the dro
down list for motherboards and has six products in the list. Thes
products run from A5:A13 in sheet 2 (more than 6 cells because it ha
alternate text included) and the correct prices are in the adjacen
column (B5:B13). In sheet 2 when the user select his/her choice o
motherboard from the drop down list in cell B10, the price of tha
should be displayed in cell C10.

I am sorry for taking up your time but i really need help with vlooku
as i havent a clue and help does not help me.

Thank you ever so much if you reply

 

[Earl Kiosterud]

Kristoficus,

The VLOOKUP will look in for whatever's in B10 looking down the first column
in Sheet2!A5:B13. It will expect to find it in one cell.

=VLOOKUP(B10, Sheet2!A5:B13, 2, FALSE)

It will return whatever is in the same row, 2nd column of A5:B13 (the third
argument = 2). It will expect an exact match (FALSE). That's only a
safeguard against having something in B10 that isn't in the table on sheet
2, and returning a value.

 

[kristoficus]

Thank you ever so much, and im sorry for having to email you - its no
your problem.

I have the basic function now thanks to you but i need to know one las
thing - is it possible to make the user have to select an item from th
list?

i.e, in the motherboard list i have:

Select a Motherboard
AMD Motherboards:
MSI-6712 KT400 Ultra
Athlon64, ViaK8T800
KT266 ATX
P4 Motherboards:
Sis650GX/962L uATX
648F Neo-L P4 S478
GA-8IDX 845

I need it to somehow alert the user to select something from the lis
if they click on either the first, second, or sixth cell - becaus
those are not real products, its only telling the user which are AMD
P4 compatable

 

[Earl Kiosterud]

Christopher,

Here's one way. Include the CPU in each item in the list:

Select a Motherboard
AMD MSI-6712 KT400 Ultra
AMD Athlon64, ViaK8T800
AMD KT266 ATX
P4 Sis650GX/962L uATX
P4 648F Neo-L P4 S478
P4 GA-8IDX 845

Earl Kiosterud
mvpearl omitthisword at verizon period net