I have columns of Part Numbers for different applications. A lot of the
different apps. take the same part#. I need to be able to type in a
description and price for each one. How do I get it to let me put my info on
all of the cells with the same Part# without physically typing it in every
cell.
I.E. If a cell in column A says part# 1111 the corresponding cell in
column F needs to say $1.00 . If colmn A says part# 1112 the corresponding
cell in Column F needs $1.50 etc.
ANY help would be appreciated...Thanks
--
Ron

Look in the help index for VLOOKUP
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Ron" wrote in message
...
I have columns of Part Numbers for different applications. A lot of the
different apps. take the same part#. I need to be able to type in a
description and price for each one. How do I get it to let me put my info
on
all of the cells with the same Part# without physically typing it in every
cell.
I.E. If a cell in column A says part# 1111 the corresponding cell in
column F needs to say $1.00 . If colmn A says part# 1112 the
corresponding
cell in Column F needs $1.50 etc.
ANY help would be appreciated...Thanks
--
Ron

Hi Ron!

As Don mentioned, try a VLOOKUP formula.


It looks like you may be doing an invoice or a Bill of Materials.

Soooo, on a different worksheet, create a listing of all your part numbers
in Column A and then the corresponding prices in Column B.

Column A (Part #) Column B (Price)
Row 1 1234 $5.00
2345 $10.00
3456 $15.00

For this example, this sheet will be named "Parts Info".

Now go back to your sheet. Let's say that the first Row for parts number
entries on your data entry worksheet is Row 2. Go to your price column
(Column F, Cell 2), enter this formula:

=VLOOKUP($A2,'Parts Info'!$A$1:$B$3,2,FALSE)

Now, enter any part number from the Parts Info sheet into Cell A2 (your
first parts entry cell).
This tells Excel to go to Parts Info and look for a matching entry in A2
(the $ locks the cell reference into Column A, but allows the Row number to
change) and if it finds a match in the Range A1:B3, it returns the value in
the second column of the search range (that's the "2" at the end of the
formula. The FALSE arguement makes it return only an exact match for the
entry in Cell A2 and makes it so you do not have to have the info in Column A
sorted in ascending order).

For your real data, if you have 500 part numbers, enter them into Column A
on the Parts Info worksheet, then enter the prices for each into Column B.
Adjust your lookup range (A1:B3) to reflect the new data (so it would be
A1:B500).

Go back to your data entry sheet and enter in a part number in Column
A...the associated price will appear in Column F.

You can even make it faster by making the entries in Column A on your data
entry sheet available via a drop menu:

Go to the "Parts Info" sheet. Highlight all your parts (just the parts) in
Column A (let's say it is A1:A500). Then, on the Menu Bar, go to
InsertNameDefine. In the bottom field ("Refers to"), it will show your
selected range:

='Parts Info'!$A$1:$A$500

In the top field ("Names in workbook"), type in the name for this range,
i.e. PartNumbers...no spaces in the name. You could also enter Parts_Number,
using an underscore, but no spaces. Click the Add button and then OK.

Now go to the first Part Number cell on your data entry sheet (i.e. A2) and
then go to DataValidation on the Menu Bar. In the dialog box that appears,
select "List" from the drop menu. Then in the "Source" field, type in
=PartNumbers or whatever you named the range of Part Numbers in the previous
step. Make sure that Ignore Blank and In-cell Dropdown boxes are checked.
Click OK.

Now, in Cell A2, you will have a drop menu arrow on the right-hand side of
the cell. You can copy/paste the cell down for as many row as you need.

Once you have that done, you can drop the menu and a list of part numbers
will appear. Once you select one, the price will pop in the corresponding
cell in Column F.

No more typing! And, if you get a bunch of part numbers and prices you need
to add, you only have to add them to the Parts Info worksheet, inserting them
into the rang for the VLOOKUP. Just make sure that the VLOOKUP range covers
all your parts and prices.



--
Greg


"Ron" wrote:

I have columns of Part Numbers for different applications. A lot of the
different apps. take the same part#. I need to be able to type in a
description and price for each one. How do I get it to let me put my info on
all of the cells with the same Part# without physically typing it in every
cell.
I.E. If a cell in column A says part# 1111 the corresponding cell in
column F needs to say $1.00 . If colmn A says part# 1112 the corresponding
cell in Column F needs $1.50 etc.
ANY help would be appreciated...Thanks
--
Ron