getting info from 2nd spreedsheet

H First I'm new to Excell. What I want to do is retreive info from a 2nd
worksheet. I have the first which is a list of part numbers and Qty's
(my inventory) and a 2nd from the manufacture which has prices,
descriptions etc.
I would like to look at a PN on worksheet 1 and look it up on worksheet
2, find the price, and enter it in a colume (cell) on worksheet 1.


--
rbell
Posted via http://ms-os.com Forum to Usenet gateway

It sounds like =vlookup() or =index(match()) would be a good choice (depending
on how your data is laid out).


Visit Debra Dalgleish's site:
http://www.contextures.com/xlFunctions02.html
for nice instructions.

rbell wrote:
>
> H First I'm new to Excell. What I want to do is retreive info from a 2nd
> worksheet. I have the first which is a list of part numbers and Qty's
> (my inventory) and a 2nd from the manufacture which has prices,
> descriptions etc.
> I would like to look at a PN on worksheet 1 and look it up on worksheet
> 2, find the price, and enter it in a colume (cell) on worksheet 1.
>
> --
> rbell
> Posted via http://ms-os.com Forum to Usenet gateway

--

Dave Peterson

Assume you have

In Sheet1 (your inventory)
-----
In cols A to C, data from row2 down

PN Qty Price
1111 100 ?
1112 200 ?
etc

In Sheet2 (from manufacturer)
------
In cols A to C, data from row2 down

PN Price
1111 100
1112 200

In Sheet1,
Put in C2: =INDEX(Sheet2!B:B,MATCH(Sheet1!A2,Sheet2!A:A,0))
Format C2 as currency if desired, then copy down

Col C will return the prices for the PN's from Sheet2
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"rbell" <[email protected]> wrote in message
news:[email protected]...
>
> H First I'm new to Excell. What I want to do is retreive info from a 2nd
> worksheet. I have the first which is a list of part numbers and Qty's
> (my inventory) and a 2nd from the manufacture which has prices,
> descriptions etc.
> I would like to look at a PN on worksheet 1 and look it up on worksheet
> 2, find the price, and enter it in a colume (cell) on worksheet 1.
>
>
> --
> rbell
> Posted via http://ms-os.com Forum to Usenet gateway
>

Hi Max First thanks for the suggestion. The sheets I have are laid out
just like your example, but when I enter it I get a #na returned. I
entered the formula in sheet1 c2 and copied it down. Any other
thoughts. Thanks ****


--
rbell
Posted via http://ms-os.com Forum to Usenet gateway

One possibility for the non-match is that the lookup PN values are text
numbers, while those in Sheet2's col A are real numbers. In which case, try
instead in Sheet1's C2: =INDEX(Sheet2!B:B,MATCH(A2+0,Sheet2!A:A,0))
The "+0" operation will coerce the text PN numbers to real numbers for
proper matching.

Another possibility is the other way around, i.e. PNs in Sheet2's col A are
text numbers, while the lookup values in Sheet1's col A are real numbers.
Try this to convert Sheet2's col A to real numbers. Select an empty cell and
copy it. Then select / right-click on Sheet2's col A > Paste Special >
Check "Add" > OK.

Try the above, let us know how it goes.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"rbell" <[email protected]> wrote in message
news:[email protected]...
>
> Hi Max First thanks for the suggestion. The sheets I have are laid out
> just like your example, but when I enter it I get a #na returned. I
> entered the formula in sheet1 c2 and copied it down. Any other
> thoughts. Thanks ****
>
>
> --
> rbell
> Posted via http://ms-os.com Forum to Usenet gateway
>

Hi Max You hit the nail on the head. The Mfg. Excell spread sheet was
all in text. I had put it on a machine with Excell 2003 (was using
Excell97)and it showed a box that pointed out they were text numbers.
It had a selection to convert them to number numbers. Also the Mfg
sheet has cost and retail prices that had to be converted so they could
be merged to my inventory. Mine has about 800 parts and the Mfg sheet
has about 28,000 numbers, everything they have. So once this was done
it was very easy to get what I want. Thanks again Max
BTW your initial formula worked after the conversion.


--
rbell
Posted via http://ms-os.com Forum to Usenet gateway

Glad to hear you got it working !
Thanks for the feedback ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"rbell" <[email protected]> wrote in message
news:[email protected]...
>
> Hi Max You hit the nail on the head. The Mfg. Excell spread sheet was
> all in text. I had put it on a machine with Excell 2003 (was using
> Excell97)and it showed a box that pointed out they were text numbers.
> It had a selection to convert them to number numbers. Also the Mfg
> sheet has cost and retail prices that had to be converted so they could
> be merged to my inventory. Mine has about 800 parts and the Mfg sheet
> has about 28,000 numbers, everything they have. So once this was done
> it was very easy to get what I want. Thanks again Max
> BTW your initial formula worked after the conversion.
>
>
> --
> rbell
> Posted via http://ms-os.com Forum to Usenet gateway
>