Product list to match price list

B

badgrandntl

I have a drop down list of different products that I sell and I want to
be able to click on one of those products in the drop down list and
then in the cost column of my spreadsheet I want it to insert the price
for that product from the drop down list. ANy help on how to do this
would be great, I trie V Lookup and IF then statments and I can't get
them to work.


Thanks Scott
 
R

Ron Coderre

Scott:
I'm fairly sure that VLOOKUP, used properly, will satisfy your request.
So, please post your formula(s) and we'll see what the forum can do for
you.

Regards,
Ron
 
B

badgrandntl

This is the formula for the first one:

VLOOKUP(Sheet2!A2,Sheet2!A42,1)

If this doesn't help I can email u the file.

Thanks very much for your hel
 
D

Dave Peterson

Say your dropdown list is in A1 (and created via Data|Validation).

Then in B1, you could put a formula like:

=if(a1="","",vlookup(a1,sheet2!a:b,2,false))
 
B

badgrandntl

Could part of my problem be that some of the suff in my list has 5"
Reynolds and I think the Parentheses could mess it up.

My first drop down list is : The information for this is on Sheet 2,
cells A1-A15
The drop down list is located on Sheet 1 cell A7 I want the price to go
into cell C7 on sheet 1.
5" GUTTER
5" REYNOLDS
5" REYNOLDS P/U
5" ROLLEX
5" ROLLEX P/U
5" ALSIDE
5" ALSIDE P/U
5" ALCOA
5" ALCOA P/U
5" COPPER
5" COPPER HR
5" QUALITY EDGE
5" QUALITY EDGE P/U
5" GALV HR
5" GALV

The price list for the above items is located on sheet 2, cells
A42-A56

0
1.15
1.10
1.15
1.10
1.20
1.15
1.23
1.18
6.75
6.75
1.23
1.23
2.20
2.40


Thanks for the help I am still trying to learn this program, Thanks for
your time Scott
 
D

Dave Peterson

Create your table in sheet2:

A1:A15 should contain your parts
B1:B15 should contain the prices

Life will be a lot simpler if your table is laid out nicely.
 
B

badgrandntl

So what formula can I do to match this stuff up, I listed the correc
cells in my above post, Dave I will take your advice and move the stuf
it make more sense that way, but I just need it so when I selec th
product the price comes up.

Thanks Scot
 
B

badgrandntl

I will move the stuff so it is moe organized. I still can't get th
formulas to work to make the price pop up when the product is selected
Withe the cells listed above could someonegive me a formula so I ca
try that, Dave I tried the formula you gave but it didn't work. I ca
email the excel document to u if it would make it easier.

Thanks Scot
 
D

Dave Peterson

After you get that table built, you can use the =vlookup() in the other post.

=if(a1="","",vlookup(a1,sheet2!a:b,2,false))
 
D

Dave Peterson

How about a little more info.

Where is the cell with the dropdown list? A1, A99, B12....
Where is the cell with the formula? B1, B99, C12, ...

What's the sheet name with the table?

What's the address of the table on that sheet? A1:B99???

What formula did you try?
 
B

badgrandntl

Where is the cell with the dropdown list? Sheet 1 A7
Where is the cell with the formula? Nowhere yet, I want to put th
price in C7

What's the sheet name with the table? Sheet 2

What's the address of the table on that sheet? A1-A21 and it is calle
FIVEGUTTER is the product table and the price table is currently i
A42-456 with no name

What formula did you try? if then statement that u posted and Vlooku
 
B

badgrandntl

I changed sheet 2 so now I hav A1-A21 named as FIVEGUTTER this is th
information for the drop down list on Sheet 1 cell A7. I put th
corresponding prices on sheet 2 , cells B1-B21 this is named a
FIVEPRICE. Now that it is setup like this how do I use the Vlookup
posted earlier. Thanks Scot
 
D

Dave Peterson

In C7:

=if(a7="","",vlookup(a7,'sheet 2'!$a$1:$b$21,2,false))

But is that sheet really named "Sheet 2" (with the space or "Sheet2" without the
space)?

And if there's nothing else on that sheet except the table, I'd use the whole
column:

=if(a7="","",vlookup(a7,'sheet 2'!$a:$b,2,false))
 
Top