Product list to match price list

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

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

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 help

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))



badgrandntl wrote:
>
> 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
>
> --
> badgrandntl
> ------------------------------------------------------------------------
> badgrandntl's Profile: http://www.excelforum.com/member.php...o&userid=31035
> View this thread: http://www.excelforum.com/showthread...hreadid=507020

--

Dave Peterson

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

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.



badgrandntl wrote:
>
> 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
>
> --
> badgrandntl
> ------------------------------------------------------------------------
> badgrandntl's Profile: http://www.excelforum.com/member.php...o&userid=31035
> View this thread: http://www.excelforum.com/showthread...hreadid=507020

--

Dave Peterson

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

Thanks Scott

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

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



badgrandntl wrote:
>
> So what formula can I do to match this stuff up, I listed the correct
> cells in my above post, Dave I will take your advice and move the stuff
> it make more sense that way, but I just need it so when I selec the
> product the price comes up.
>
> Thanks Scott
>
> --
> badgrandntl
> ------------------------------------------------------------------------
> badgrandntl's Profile: http://www.excelforum.com/member.php...o&userid=31035
> View this thread: http://www.excelforum.com/showthread...hreadid=507020

--

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?



badgrandntl wrote:
>
> I will move the stuff so it is moe organized. I still can't get the
> 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 can
> try that, Dave I tried the formula you gave but it didn't work. I can
> email the excel document to u if it would make it easier.
>
> Thanks Scott
>
> --
> badgrandntl
> ------------------------------------------------------------------------
> badgrandntl's Profile: http://www.excelforum.com/member.php...o&userid=31035
> View this thread: http://www.excelforum.com/showthread...hreadid=507020

--

Dave Peterson

Where is the cell with the dropdown list? Sheet 1 A7
Where is the cell with the formula? Nowhere yet, I want to put the 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 called FIVEGUTTER is the product table and the price table is currently in A42-456 with no name

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

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

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))



badgrandntl wrote:
>
> I changed sheet 2 so now I hav A1-A21 named as FIVEGUTTER this is the
> information for the drop down list on Sheet 1 cell A7. I put the
> corresponding prices on sheet 2 , cells B1-B21 this is named as
> FIVEPRICE. Now that it is setup like this how do I use the Vlookup u
> posted earlier. Thanks Scott
>
> --
> badgrandntl
> ------------------------------------------------------------------------
> badgrandntl's Profile: http://www.excelforum.com/member.php...o&userid=31035
> View this thread: http://www.excelforum.com/showthread...hreadid=507020

--

Dave Peterson

I got it to work, thanks for your help, I really appreciate it. Scott