Help!

A

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.
 
R

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
 
A

Adam

Fantastic, thanks Roger.

Roger Govier said:
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
Click to expand...
 
A

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.
 
D

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
 
R

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
 
Top