Using drop down lists to set value for formula

[Lovelock, David]

I have a worksheet that I am trying to calculate material prices on.
have the user input Length, Width and calculate SqFeet. I then let th
user choose the material to be sued from a lsit. The lsit is reference
from sheet2. I would like to assign the price to each material fro
sheet2 and when the user selects the material have the price b
multiplied by the SqFeet and displayed in a price field. I have create
the list, I just can't figrue out how to assign dollar values t
choices or use them in a formula.

Any help would be appreciated

+----------------------------------------------------------------
| Attachment filename: pricing_inprocess.xls
|Download attachment: http://www.excelforum.com/attachment.php?postid=361987
+----------------------------------------------------------------

 

[Debra Dalgleish]

There's a sample file on my web site that uses Data Validation and
VLookup to complete an order form. You could use the same technique for
your pricing:

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

Look for the heading: Order Form (DataValOrder.xls)

 

[Gord Dibben]

David

Sheet2 column A has list of materials from A2:A10. Make that list a named
range, say matlist.
Sheet2 column B has list of corresponding price/square foot in B2:B10.

Sheet1 C2 has square feet calculated.
Sheet1 D2 has a Data>Validation drop-down list created from the list of
material on Sheet2. Data>Validation>List enter =matlist

Sheet1 E2 has this formula =VLOOKUP(D2,Sheet2!$A$2:$B$10,2,FALSE)*C2

Gord Dibben XL2002

 

[Max]

Try this:

In Sheet2

Put in
C2: 1
C3: 2
(and so on down col C, if there are more than 2 items)

In Sheet1:

Right-click on the drop menu > Format Control > Control tab

Put
In Cell link: Sheet1!$E$6

Click OK

Put in C9: =OFFSET(Sheet2!$C$1,MATCH(E6,Sheet2!C:C,0)-1,-1,1,1)*E4

C9 returns the product of the SF price and the SF calculated in E4

 

[Max]

Missed out the line earlier...

Format C9 as Currency

--
hth
Max
-----------------------------------------
Please reply in newsgroup

Use xdemechanik
<at>yahoo<dot>com
for email
-------------------------------------------

 

[Lovelock, David]

Max - Your suggestion worked. Thank you, now is it possible for you t
explain to me why? I am clueless on this one. Feel free to e-mail me i
you like... (e-mail address removed)

Thanks for your help!

Gord - I haven't tried your suggestion yet, I will though. Max's jsu
looked ewasier to make happen. Now I just need to understand why

 

[Max]

you're welcome David!

glad to know it worked for you.

Here's some explanation:

You already had a combo box drawn
positioned across C66 (from the Forms Toolbar)

This combo box has a Cell link feature which could be made use of
to proxy the selection made in the combo-box
(as a selection number, so as to speak) for use in a downstream formula.

After naming the cell-link to be E6 (E6 is arbitrary, it can be any empty
cell),
in E6 will appear a number corresponding to the selection made in the
combo-box,
viz. if the 1st item in the combo-box list is selected (ie 1/4 Clear Float),
a number 1 will appear in E6, if the 2nd item is then selected (1/4 Clear
Mirror),
the number in E6 will change to 2. And so on.

The number appearing in E6 is then made use of in the OFFSET & MATCH formula
in C9
to return the corresponding SF price. This SF price is then multiplied by
the SF calculated
in E4 to give the $$value that is wanted.

MATCH(E6,Sheet2!C:C,0) will match exactly* the value appearing in E6
(i.e. 1or 2) against the values appearing in col C in Sheet2
(remember we had earlier created the numbers 1,2 in C1:C2),
and return the position of the matched value
ie a "row" number corresponding to the match found.

*setting the match-type (3rd param) in MATCH = 0 will ensure this

For example, if E6 = 2, then the exact match is
with the value 2 in cell C3 of Sheet2, ie row 3.

This row number (3) has then to be subtracted by one so as to give
the correct input row number for use in the OFFSET formula.

The OFFSET formula which is anchored against cell C1 in Sheet2,
regards cell C3 as being row #2, ie 2 rows below the anchor row 1.

The column number in the OFFSET is set at -1,
as we want to capture the SF price,
which is in a column to the left of the anchor column C
(going to the left of anchor col in OFFSET means "negative").

Do check up more details on OFFSET, MATCH
and control properties for combo boxes in Excel's Help

 

[Lovelock, David]

Thanks again Max. I was able to follow your instruction and take th
first example given and apply that same process to 4 other cirteri
needed for our quoting tool.:

 

[Max]

Pleased to hear that, David.
Thanks for feedback

cheers
Max