populate unit price dependent on area and codes selected.

[carrieann]

I am trying to create an invoice. I have 5 different areas. i have my invoice set up with a drop down to select one of the 5 areas. Then below I have a drop down of codes. And when I select a code it will automatically populate the descriptions. What I cant figure out is how do I auto populate the price based on the area it is in.

Each are has the same codes, but the prices are different.
example: code aa00 in job area 1 is $2, in job area 2 it is $5, job area 3 it is $10.

my price list is set up on a different sheet. this is how it is set up.

code

description

job area 1

job area 2

job area 3

job area 4

job area 5

this is the main sheet the red cloud is the cell I am looking to populate.

 

[awoohaw]

Try:

=INDEX($A$1:$G$100,Match(cellrefCODE,$A$1:$A$100,0),Match(cellRefJobArea,$A$1:$G$1,0))

This is assuming the dropdown for job area has the values in cells $C$1:$G$1: {"job area 1"," job area 2"," job area 3"," job area 4"}
Otherwise, please post some sample data and expected returned values.

 

[carrieann]

Job area is D17
Code area is a22
and unit price is f22

 

[awoohaw]

i'm not sure what i'm supposed to do with that information.

plugging those references in give me this:
from this:
=INDEX($A$1:$G$100,Match(cellrefCODE,$A$1:$A$100,0),Match(cellRefJobArea,$A$1:$G$1,0))
to this (so put this in Cell F22):
=INDEX($A$1:$G$100,Match(A22,$A$1:$A$100,0),Match(D17,$A$1:$G$1,0))

If this doesn't work for you: Then I will politely ask again: Please post sample data and expected returns?