Help!

[Adam]

Can anyone tell me if this is possible....

I have a spreadsheet from my supplier that lists all stock items, pricing &
current stock. I also have a spreadsheet showing a selection of items that my
supplier sells but not all. Is it possible to get my suppliers spreadhseet to
update the stock on the items i sell to update automatically rather than me
having to manually go through and change them myself?

Thanks,
Adam.

[Roger Govier]

Hi Adam

I would copy the list from your suppliers workbook into your workbook first.
Right click on the tab in his workbook>Move or Copy>To Book select the name
of your workbook>Select Copy>OK

Now rename his sheet to Supplier, and supposing the list of product names is
in Column A starting at A2, with prices in Column B starting at B2

On your sheet, again assuming products are in Column A and prices in column
B, enter in cell B2
=VLOOKUP(A2,'Suppliers'!$A$2:$B$1000,2,0)
and copy down column B as far as required.

Change ranges to suit.


Regards

Roger Govier


Adam wrote:
> Can anyone tell me if this is possible....
>
> I have a spreadsheet from my supplier that lists all stock items, pricing &
> current stock. I also have a spreadsheet showing a selection of items that my
> supplier sells but not all. Is it possible to get my suppliers spreadhseet to
> update the stock on the items i sell to update automatically rather than me
> having to manually go through and change them myself?
>
> Thanks,
> Adam.

[Adam]

Fantastic, thanks Roger.

"Roger Govier" wrote:

> Hi Adam
>
> I would copy the list from your suppliers workbook into your workbook first.
> Right click on the tab in his workbook>Move or Copy>To Book select the name
> of your workbook>Select Copy>OK
>
> Now rename his sheet to Supplier, and supposing the list of product names is
> in Column A starting at A2, with prices in Column B starting at B2
>
> On your sheet, again assuming products are in Column A and prices in column
> B, enter in cell B2
> =VLOOKUP(A2,'Suppliers'!$A$2:$B$1000,2,0)
> and copy down column B as far as required.
>
> Change ranges to suit.
>
>
> Regards
>
> Roger Govier
>
>
> Adam wrote:
> > Can anyone tell me if this is possible....
> >
> > I have a spreadsheet from my supplier that lists all stock items, pricing &
> > current stock. I also have a spreadsheet showing a selection of items that my
> > supplier sells but not all. Is it possible to get my suppliers spreadhseet to
> > update the stock on the items i sell to update automatically rather than me
> > having to manually go through and change them myself?
> >
> > Thanks,
> > Adam.
>

[Adam]

Hi Roger

Sorry to bother you again.....I've followed the instructions below but keep
getting an N/A message in the cell i want my answer. Here is what i've done.

Copied my suppliers worksheet into my workbook on a seperate worksheet named
Namber. The fields i need to work with are product code (column D) and Stock
(column F). Therefore, on my worksheet in column F row 2 i have entered the
following... =VLOOKUP(D2,'Namber'!$D$2:$F$1000,2,0)

What have i done wrong?
Thanks,
Adam.

"Roger Govier" wrote:

> Hi Adam
>
> I would copy the list from your suppliers workbook into your workbook first.
> Right click on the tab in his workbook>Move or Copy>To Book select the name
> of your workbook>Select Copy>OK
>
> Now rename his sheet to Supplier, and supposing the list of product names is
> in Column A starting at A2, with prices in Column B starting at B2
>
> On your sheet, again assuming products are in Column A and prices in column
> B, enter in cell B2
> =VLOOKUP(A2,'Suppliers'!$A$2:$B$1000,2,0)
> and copy down column B as far as required.
>
> Change ranges to suit.
>
>
> Regards
>
> Roger Govier
>
>
> Adam wrote:
> > Can anyone tell me if this is possible....
> >
> > I have a spreadsheet from my supplier that lists all stock items, pricing &
> > current stock. I also have a spreadsheet showing a selection of items that my
> > supplier sells but not all. Is it possible to get my suppliers spreadhseet to
> > update the stock on the items i sell to update automatically rather than me
> > having to manually go through and change them myself?
> >
> > Thanks,
> > Adam.
>

[Dave Peterson]

Maybe it's as simple as your data doesn't match.

One common problem is extra spaces in the lookup value or table
(leading/trailing or embedded).

Another problem is when the data looks numeric.

If D2 is really text ('123) and the table had 123, then it won't match.

Debra Dalgleish has some tips at:
http://contextures.com/xlFunctions02.html#Trouble

Adam wrote:
>
> Hi Roger
>
> Sorry to bother you again.....I've followed the instructions below but keep
> getting an N/A message in the cell i want my answer. Here is what i've done.
>
> Copied my suppliers worksheet into my workbook on a seperate worksheet named
> Namber. The fields i need to work with are product code (column D) and Stock
> (column F). Therefore, on my worksheet in column F row 2 i have entered the
> following... =VLOOKUP(D2,'Namber'!$D$2:$F$1000,2,0)
>
> What have i done wrong?
> Thanks,
> Adam.
>
> "Roger Govier" wrote:
>
> > Hi Adam
> >
> > I would copy the list from your suppliers workbook into your workbook first.
> > Right click on the tab in his workbook>Move or Copy>To Book select the name
> > of your workbook>Select Copy>OK
> >
> > Now rename his sheet to Supplier, and supposing the list of product names is
> > in Column A starting at A2, with prices in Column B starting at B2
> >
> > On your sheet, again assuming products are in Column A and prices in column
> > B, enter in cell B2
> > =VLOOKUP(A2,'Suppliers'!$A$2:$B$1000,2,0)
> > and copy down column B as far as required.
> >
> > Change ranges to suit.
> >
> >
> > Regards
> >
> > Roger Govier
> >
> >
> > Adam wrote:
> > > Can anyone tell me if this is possible....
> > >
> > > I have a spreadsheet from my supplier that lists all stock items, pricing &
> > > current stock. I also have a spreadsheet showing a selection of items that my
> > > supplier sells but not all. Is it possible to get my suppliers spreadhseet to
> > > update the stock on the items i sell to update automatically rather than me
> > > having to manually go through and change them myself?
> > >
> > > Thanks,
> > > Adam.
> >

--

Dave Peterson

[Roger Govier]

Hi Adam

Sorry of the long delay, I have been out most of the day.
Since your table is columns D through F, and the value is in column F, then
the offset in the formula needs to be 3 instead of 2

=VLOOKUP(D2,'Namber'!$D$2:$F$1000,3,0)

Regards

Roger Govier


Adam wrote:
> Hi Roger
>
> Sorry to bother you again.....I've followed the instructions below but keep
> getting an N/A message in the cell i want my answer. Here is what i've done.
>
> Copied my suppliers worksheet into my workbook on a seperate worksheet named
> Namber. The fields i need to work with are product code (column D) and Stock
> (column F). Therefore, on my worksheet in column F row 2 i have entered the
> following... =VLOOKUP(D2,'Namber'!$D$2:$F$1000,2,0)
>
> What have i done wrong?
> Thanks,
> Adam.
>
> "Roger Govier" wrote:
>
>
>>Hi Adam
>>
>>I would copy the list from your suppliers workbook into your workbook first.
>>Right click on the tab in his workbook>Move or Copy>To Book select the name
>>of your workbook>Select Copy>OK
>>
>>Now rename his sheet to Supplier, and supposing the list of product names is
>>in Column A starting at A2, with prices in Column B starting at B2
>>
>>On your sheet, again assuming products are in Column A and prices in column
>>B, enter in cell B2
>>=VLOOKUP(A2,'Suppliers'!$A$2:$B$1000,2,0)
>>and copy down column B as far as required.
>>
>>Change ranges to suit.
>>
>>
>>Regards
>>
>>Roger Govier
>>
>>
>>Adam wrote:
>>
>>>Can anyone tell me if this is possible....
>>>
>>>I have a spreadsheet from my supplier that lists all stock items, pricing &
>>>current stock. I also have a spreadsheet showing a selection of items that my
>>>supplier sells but not all. Is it possible to get my suppliers spreadhseet to
>>>update the stock on the items i sell to update automatically rather than me
>>>having to manually go through and change them myself?
>>>
>>>Thanks,
>>>Adam.
>>