English Deutsch Français Italiano Español Português 繁體中文 Bahasa Indonesia Tiếng Việt ภาษาไทย
All categories

i'm having a problem finding a formula that will work for my situation. i work in construction, and we have these ordering forms we fill out for our big jobs.

everything has an item name, item number and measurement. there are 12 item names, each with around 10 item numbers and 8 item measurements. i want to be able to fill in a name, number and measurment in each cell and have it sum to a set price.

for example:

S125 + 162 + 25GA = $100
S125 + 250 + 25GA = $200
S162 + 800 + 18GA = $600

is there any way to do this?? i don't mind at all entering in all of the different combinations and prices on a seperate spreadsheet for it to link to... i just want my guys to be able to enter the measurements into the form and have the price fill in without them having to look it up.

helppp!!!

2007-12-06 09:17:57 · 3 answers · asked by underpre$$ure 2 in Computers & Internet Software

to answer, "what determines the price?":

the price is already set. it is set up so that you find your item number in the first column, then find your measurment in the top row, and that will lead you to your price in the columns/rows following... confused yet? haha, i'm not quite sure how to explain... like, the first column would have all of the item numbers, for example:

162
250
350
362

then the top row has all of the item measurements, for example:

25GA 22GA 20GA 18GA

then you follow to find your price, for example:

25GA 22GA 20GA 18GA
162 $100 $110 $120 $115
250 $200 $220 $225 $205
350 $110 $115 $130 $180

...those aren't the actually prices, but that's how it works.

2007-12-06 10:47:36 · update #1

3 answers

Functions you can use are VLOOKUP and MATCH.
Let's say your item numbers are in column A and your sizes are in row 1. Here's a 2x2 example

item 25GA22GA
162 $100 $110
250 $200 $220

Now, enter an item number into cell A10 and a size into cell B10. Into C10 type

=VLOOKUP(A10,B2:C3,
MATCH(B10,B1:C1,0))

(of course, put this all on 1 line. In Yahoo Answers, things get truncated if the line is too long)

translation:
1. match the size you entered into B10 with a size in the range B1:C1. The result is a column number (with B=1)
2. look for the item number you entered into A10 in column A. The result is a row number (with row 2 = 1)
3. Return the value (price) in the array B2:C3 that is in the column and row you matched above.

Try it and you'll get the hang of it.

To get fancier, you can provide a "validation" list in cells A10 and B10 so that the user can just pick the item number and size from a drop down list.

2007-12-06 12:46:18 · answer #1 · answered by sevenletters4me 6 · 0 0

Mel

if you could send me a dummy file with information as you want (showing obviously the source table for the look up values, prices, part, etc)) i 'll be more than glad to give you a goood solution.. either with array formulas or with macros

pepedean@yahoo.com

2007-12-13 13:36:57 · answer #2 · answered by JOSSDEAN 3 · 0 0

what determines the price? It is possible to do this. I would just need more details.

I get it. I will have to think on that for a second. I will get back to you.

2007-12-06 18:34:54 · answer #3 · answered by mamba 1 · 0 0

fedest.com, questions and answers