I want users to type in a part number in one cell "A1" then in cell "A2" I
would like the description of the part to appear. In cell A3 the price of
part should show up, etc
I have set so part numbers are on a master sheet (column A) with description
(column B) and Price (column C), etc beside. Each seperate sheet in file
uses different part combinations. I need the formula to "hunt" for the part
number in a range and return the corresponding values in the adjacent cells.
Example typing number PT1(part 1) in cell A1 returns corresponding part 1
description in cell B1 and part price in Cell C1. The tricky part is that if
in cell A2 I type a part number that isnt the second part in the parts master
list then the statements does not work. I realize that I will have to have
separate statements for columns b & C. Please help

"Whayne" wrote:

I want users to type in a part number in one cell "A1" then in cell "A2" I
would like the description of the part to appear. In cell A3 the price of
part should show up, etc
I have set so part numbers are on a master sheet (column A) with description
(column B) and Price (column C), etc beside. Each seperate sheet in file
uses different part combinations. I need the formula to "hunt" for the part
number in a range and return the corresponding values in the adjacent cells.
Example typing number PT1(part 1) in cell A1 returns corresponding part 1
description in cell B1 and part price in Cell C1. The tricky part is that if
in cell A2 I type a part number that isnt the second part in the parts master
list then the statements does not work. I realize that I will have to have
separate statements for columns b & C. Please help


I'm not 100% clear with what you want to do but if I'm right, and you want
to lookup the value next to your part number then HLookup sounds like a
better function than an IF Statement.

Hlookup is as follows:
=Hlookup(LookupVal,TableArray,Index,Lookup)

So assuming your lookup value is in Column A, =Hlookup("Part1",A1:C3,1)
'Would return the value in column B that is next to your lookup item. to
lookup the value is column C, increment the index (i.e.
=Hlookup("Part1",A1:C3,2) Hope this makes sense.

If I've missed your objective, let me know and I'll have another try!

James Nicholls

Yeah I was worried that I wouldnt make sense. I made a mistake by putting
A1-A3 instead of A1-C1. The data is arranged in rows simular to below on
page1 or "master page". On Page2 how can I make a formula in cell B1 that
looks at part number in cell A1 and matches it to a part number on page1
thereby giving the value in cell B2 to be the description of part number in
A1. I would then repeat this for cell C1 to use part number in cell A1 to
find the price. I can swap the order for part description and the part
numbers on Page1 if necessay. Thanks for your input.
Page1
Parts Part # Sell
Grease G1 $3.50
Delvac 1300 Sup 15W-40 (Eng.) Del $5.33
Trans./Diff Oil W30 TO-4 Trans $201.15
Delvac 1210 Hyd. Oil DelDif $4.38
Tandem Drive Housing Tan $7.52
Circle Drive Housing W30Cir $10.24




"Jim Nicholls" wrote:



"Whayne" wrote:

I want users to type in a part number in one cell "A1" then in cell "A2" I
would like the description of the part to appear. In cell A3 the price of
part should show up, etc
I have set so part numbers are on a master sheet (column A) with description
(column B) and Price (column C), etc beside. Each seperate sheet in file
uses different part combinations. I need the formula to "hunt" for the part
number in a range and return the corresponding values in the adjacent cells.
Example typing number PT1(part 1) in cell A1 returns corresponding part 1
description in cell B1 and part price in Cell C1. The tricky part is that if
in cell A2 I type a part number that isnt the second part in the parts master
list then the statements does not work. I realize that I will have to have
separate statements for columns b & C. Please help


I'm not 100% clear with what you want to do but if I'm right, and you want
to lookup the value next to your part number then HLookup sounds like a
better function than an IF Statement.

Hlookup is as follows:
=Hlookup(LookupVal,TableArray,Index,Lookup)

So assuming your lookup value is in Column A, =Hlookup("Part1",A1:C3,1)
'Would return the value in column B that is next to your lookup item. to
lookup the value is column C, increment the index (i.e.
=Hlookup("Part1",A1:C3,2) Hope this makes sense.

If I've missed your objective, let me know and I'll have another try!

James Nicholls

Just do a VLOOKUP() it should do the job.


Hope it helps.


Whayne Wrote:
I want users to type in a part number in one cell "A1" then in cell "A2"
I
would like the description of the part to appear. In cell A3 the price
of
part should show up, etc
I have set so part numbers are on a master sheet (column A) with
description
(column B) and Price (column C), etc beside. Each seperate sheet in
file
uses different part combinations. I need the formula to "hunt" for the
part
number in a range and return the corresponding values in the adjacent
cells.
Example typing number PT1(part 1) in cell A1 returns corresponding part
1
description in cell B1 and part price in Cell C1. The tricky part is
that if
in cell A2 I type a part number that isnt the second part in the parts
master
list then the statements does not work. I realize that I will have to
have
separate statements for columns b & C. Please help


--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=388844