Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Help!
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. |
#2
|
|||
|
|||
Hi Adam
I would copy the list from your suppliers workbook into your workbook first. Right click on the tab in his workbookMove or CopyTo Book select the name of your workbookSelect CopyOK 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. |
#3
|
|||
|
|||
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 workbookMove or CopyTo Book select the name of your workbookSelect CopyOK 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. |
#4
|
|||
|
|||
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 workbookMove or CopyTo Book select the name of your workbookSelect CopyOK 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. |
#5
|
|||
|
|||
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 workbookMove or CopyTo Book select the name of your workbookSelect CopyOK 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 |
#6
|
|||
|
|||
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 workbookMove or CopyTo Book select the name of your workbookSelect CopyOK 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|