ignorant excel user trying to build pricing form

[Rick]

A little help, please. I think what I'm trying to do is pretty basic and
easy... I'm just not familiar enough with Excel to know how to do it.

I am using Excel98 for Mac.

I'm trying to build a pricing form. So, in the first field the user has
a choice of items from a pull down menu (approx fifteen items) and
selects one. If the choice is Item A, then I'd like the spread sheet to
insert a predetermined unit price in the next field which corresponds to
Item A. The user will then enter a quantity in the third field and the
spread sheet will multiply the two to get a price in the fourth field.

I have figured out how to build my pull down menu list
(data>validation>list). I prefer to have my source for this pull down
list on another worksheet, but I can't seem to figure out how to make
excel do this (using source from same sheet was pretty easy to figure
out). Not a big deal if I have to have my source list for the pull down
choices on the same worksheet, but it would be cleaner if that source
list were on another sheet.

I'm guessing I have to build a series of "if/then" commands following
the pull down list in order to get the spread sheet to drop the prices
in for me. I don't know how to write if/then formulas, though... any
tips to get me started on that?

Will I have to enter the all unit prices for the "if/then" commands into
the formula? Or, can I have a corresponding source list for this? I
wonder how I write the formula so excel knows which price corresponds to
which item from the first source list?

Is there a sample pricing spread sheet somewhere online that I can
download and reverse engineer?

TIA

- Rick

 

[Gregg]

First, on another sheet - if you wish, build a table of your items
(column A) and corresponding prices (column B).

For example, I'll assume the table is in cells A1:B50 of sheet
"MyPriceList", and that your cell with the drop list of items is in
cell D25 of another sheet.

Back in the cell next to the item drop list (where the price will go),
enter the formula...

=vlookup(D25,MyPriceList!$A$1:$B$50,2,False)

That should work.