Formula between Worksheets and Drop down list

  • Thread starter Thread starter jason4957
  • Start date Start date
J

jason4957

I am new to the site but have already found some great info by searching
through the threads.
However there is one thing I cant find an answer for anywhere, and
it's driving me crazy because it doesn't seem like it should be as hard
as I must be making it. I really need to make this work...o.k here
goes..

On Sheet1 A5,A100 shows a Description of a product, then B5,B100
gives the prices of that product.
On Sheet2, I have a created drop down list for A5,A80 which lets
me choose from the products I listed on Sheet1 A5,A100

here's where the problem is...

On Sheet2, I need the B Column to automatically give the price of
whatever product that I select from the drop down list in Column A.
Once I get that I can then put in a Qty. in coulmn C and enter a
formula in column D to give a total.

I'm using the most current Excel. Thanks a million to anyone who has
some insight for this.

Jason
 
Jason,

Try =Vlooup(A5,Sheet1!$A$100,2,0) in cell B5 of Sheet2 and copy down to
B80

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
The proper name for the function is "vlookup(your formula)". That
should get rid of your #name error
 
jason,

thanks for the post, it got rid of the #NAME error.

now it is giving me "#N/A"

any thoughts?
 
Jason,

It may have to do with typing out the sheet1!

make it easy on yourself. Click into Sheet1 and highlight the entire
range of product names and product prices. Once highlighted, name the
range "pricelist." (you name a range by clicking into the box to the
left of the formula entry line that should indicate the cell you are
in, or the range you have selected....in this case the box should read
"A1:B10000" or whatever. Click in there and type instead "pricelist").
Now that range has a name insted of a number that can be used in any
other workbook in the spreadsheet.

Now go to sheet 2 and select the first cell that you want a price to be
entered. Type "=vlookup([click on the cell to the left],pricelist,2).

note the "2" tells Excel to return the value in the 2nd column next to
the matching product name.....you could add 10 other columns to your
price list (rename it using all 10 columns) and then change your
vlookup formula to return the value, it say, the sixth column
instead...\

hope this helps
 
jason,

thanks again for your help. if i try to select 2 columns (A&B) i get
the error shown below

i did get it to give a price however by using your formula and just
selecting the A column to make the drop down list from, but the price
it puts in B seems almost to be random, it's not the price next to the
specific item in sheet 1.

thanks again for all help

Attachment filename: picture3.jpg
Download attachment: http://www.excelforum.com/attachment.php?postid=415235
 
Ok. Sometimes Excel gets funcky when you have text lists.....Excel
likes its lists in order, e.g. ascending descending....sometimes with
text lists, you have the same text string more than once in the list
and excel balks with lookup functions.

Try this.

Your going to number your products. Go to Sheet 1, and insert TWO
columns to the left of your product descriptions. Then insert the
numbers 1, 2, 3, etc. and then drag them down until every product has a
number next to it.

Lets assume your sheet now looks like this

A1 B1 C1 D1
1 Product X $100
*etc*

Then go to the column A to the left of the price list (should be empty)
and type this formula: "=A1&" -- "&B1. Copy this down to the last
product. Now highlight all of these formulas in Column A and name that
selection "dropdownlist". Now select the entire range A1:D:the bottom
right-hand corner of your list. Name that selection "bigpricelist".

Go to Sheet2. I don't know what kind of combo box you are using, but
here you want to do a simple validation list box.....

click in a cell on Sheet 2 that you want the drop-down box to be in.
Lets call it A1. Click on "Data" at the top menu. Click "Validation".
In the Allow Box, click "List." In the formula line type
"=dropdownlist". Click ok.

Now when you click in that cell you get an internal drop-down list with
all of your numbered products.

Now click in cell B1. Type "=vlookup(A1,bigpricelist,4)"

I think that will solve your problem (Excel can now see the list in
ascending order).

cheers.
 
Back
Top