Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do update inventory
I have worksheet that has a list of my inventory. On a day to day basisi i
download a new file that has a complete list of inventory for my wholesaler. What i am trying to do is take that data from my wholesaler find the products that i am intrested in which will then automatically update any inventory changes to the products qantity. So to sumerize i need to Find specific data from one file compare that to my exisiting file and update one cells values depending on if there are changes. Currently i am going line by line using the find fuction from one book to find the data in the other book then looking to see if values have changed and updating as needed. This is very time consuming. Any help on this would be great. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do update inventory
You can't make this 100% automatic, but you should be able to take a lot of
the work out of it. To do it the way I am about to suggest, you need a 'helper' column to put the formula into, and an extra cell on your worksheet to change the reference to the other workbook's data in. The reason I'm suggesting this is that I am going to presume that the daily updates may not always be exactly the same list, and may just contain the updated items/quantities. The helper column is going to show the values that need to go into your current quantity column and you'll get them there by using Copy and Paste Special | Values Assuming a pretty simple setup in your to-be-updated inventory list, with a unique item description or stock number in column A and the current inventory quantity in column B, and we will use column C as the helper column. Also assumes your inventory list starts in row 2, not row 1 in your to-be-updated workbook. Next assumption, that you have an available, unused cell at location D1 (any unused cell will do, just remember it's location/address). Start by creating an address to the workbook that contains the daily updated information, and it will be less confusing if you have one of those workbooks open while doing this. This will go into D1. Start by typing two single quote marks into D1, then put the name of the other workbook within square brackets, followed by the name of the sheet in the other workbook that the updated list is on followed by an exclamation point (!) and finally, the absolute address of the entire range in the other workbook starting with where the first matching description/stock number entry appears, down to the last location where the updated quantity appears. NOTE: it is assumed that the description is in a column to the left of the quantity on the daily update sheet. That may have been a little hard to follow, so here is an example: The daily update workbook's name is "Updates From Store 4" The sheet that the update information is named "November 23" and there is lots of data on it, but the first item description/stock number to match up with is at cell D7, and the current quantity is over in column G and the list goes down 50 items to G57. So in our cell D1 we would enter: ''[Updates From Store 4]November 23!$D$7:$G$57 Special note: that example starts with two single quote marks, not a double-quote mark. It should appear in cell D1 as: '[Updates From Store 4]November 23!$D$7:$G$57 I think you're done with the hard part. If you made it thru that, you're 85% there. Now to set up the formulas to get the updated values. In cell C3 put this formula (which will no doubt need some changes for your real world setup): =IF(ISNA(VLOOKUP(A2,INDIRECT($D$1),4,FALSE)),B2,VL OOKUP(A2,INDIRECT($D$1),4,FALSE)) Extend/fill that formula all the way down your sheet for all of your inventory items. What the formula says is that if it cannot find a match in the update sheet to the entry in column A in this book, just echo the current inventory quantity, but if it finds a match to that description/stock number, then get the quantity from the update workbook/sheet also. That is what the ,4, is doing - getting the value in the 4th column of the lookup table ($D$7:$G$57, remember? ... D=1, E=2, F=3, G=4th column). So change the ,4, to whatever it needs to be to find the new inventory quantities. Now you select all of the entries in column C (updated values) and use Edit | Copy (or [Ctrl]+[C]) to copy the values to the clipboard. Then click in the first current inventory entry in column B, and use Edit | Paste Special and select the [Values] option on the dialog box and click [OK] - all updated values will be placed into column B, replacing the older numbers. Now on to all of the caveat's and what-about's: As soon as you close the other workbook (containing the daily update values), the entries in column C with the formulas are going to change to #REF entries. This is because INDIRECT() doesn't work unless the other workbook is open. So, to hide this ugliness, you could simply hide the column with the ugliness in it until the next update. Why did I use INDIRECT with the setup in cell D1? Because as I said, and as you indicated, the update lists may vary from day to day. When you get a new update workbook each day, you can make edits in cell D1 to quickly change the name of the workbook, the sheet the updates are on and the range where the update information is at. You would first open up the other book along with the inventory workbook and make the edits in cell D1 and the values in column C should change from #REF to actual values as soon as you exit from cell D1. Suggestion: since I suspect that the list if items in the to-be-updated workbook will be fairly static, you could start recording a macro at the point where you start to select the entries in column C with the new inventory quantities in them. Then stop recording it after you get the values pasted into the proper column. Then each day you could simply: Open the new workbook with the updated inventory list Change the reference in D1 as required Run the macro you recorded to move the values from the formula column into the actual data column. Two minutes of effort and you're done. If you need any more detailed help with this or if I've confused you to no end, feel free to contact me at (remove spaces) HelpFrom @ jlathamsite.com I know it was a long read, with some complex instructions, but I believe it will provide you with a long-lasting, very flexible solution. "Stephen" wrote: I have worksheet that has a list of my inventory. On a day to day basisi i download a new file that has a complete list of inventory for my wholesaler. What i am trying to do is take that data from my wholesaler find the products that i am intrested in which will then automatically update any inventory changes to the products qantity. So to sumerize i need to Find specific data from one file compare that to my exisiting file and update one cells values depending on if there are changes. Currently i am going line by line using the find fuction from one book to find the data in the other book then looking to see if values have changed and updating as needed. This is very time consuming. Any help on this would be great. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do update inventory
Stephen,
I found a typo after I hit the [post] button. The two examples for what to put into cell D1 are wrong, they are missing a single quote mark in front of the ! point in each example. They should look like this instead: ''[Updates From Store 4]November 23'!$D$7:$G$57 as you type it in, and this after you've left the cell: '[Updates From Store 4]November 23'!$D$7:$G$57 "JLatham" wrote: You can't make this 100% automatic, but you should be able to take a lot of the work out of it. To do it the way I am about to suggest, you need a 'helper' column to put the formula into, and an extra cell on your worksheet to change the reference to the other workbook's data in. The reason I'm suggesting this is that I am going to presume that the daily updates may not always be exactly the same list, and may just contain the updated items/quantities. The helper column is going to show the values that need to go into your current quantity column and you'll get them there by using Copy and Paste Special | Values Assuming a pretty simple setup in your to-be-updated inventory list, with a unique item description or stock number in column A and the current inventory quantity in column B, and we will use column C as the helper column. Also assumes your inventory list starts in row 2, not row 1 in your to-be-updated workbook. Next assumption, that you have an available, unused cell at location D1 (any unused cell will do, just remember it's location/address). Start by creating an address to the workbook that contains the daily updated information, and it will be less confusing if you have one of those workbooks open while doing this. This will go into D1. Start by typing two single quote marks into D1, then put the name of the other workbook within square brackets, followed by the name of the sheet in the other workbook that the updated list is on followed by an exclamation point (!) and finally, the absolute address of the entire range in the other workbook starting with where the first matching description/stock number entry appears, down to the last location where the updated quantity appears. NOTE: it is assumed that the description is in a column to the left of the quantity on the daily update sheet. That may have been a little hard to follow, so here is an example: The daily update workbook's name is "Updates From Store 4" The sheet that the update information is named "November 23" and there is lots of data on it, but the first item description/stock number to match up with is at cell D7, and the current quantity is over in column G and the list goes down 50 items to G57. So in our cell D1 we would enter: ''[Updates From Store 4]November 23!$D$7:$G$57 Special note: that example starts with two single quote marks, not a double-quote mark. It should appear in cell D1 as: '[Updates From Store 4]November 23!$D$7:$G$57 I think you're done with the hard part. If you made it thru that, you're 85% there. Now to set up the formulas to get the updated values. In cell C3 put this formula (which will no doubt need some changes for your real world setup): =IF(ISNA(VLOOKUP(A2,INDIRECT($D$1),4,FALSE)),B2,VL OOKUP(A2,INDIRECT($D$1),4,FALSE)) Extend/fill that formula all the way down your sheet for all of your inventory items. What the formula says is that if it cannot find a match in the update sheet to the entry in column A in this book, just echo the current inventory quantity, but if it finds a match to that description/stock number, then get the quantity from the update workbook/sheet also. That is what the ,4, is doing - getting the value in the 4th column of the lookup table ($D$7:$G$57, remember? ... D=1, E=2, F=3, G=4th column). So change the ,4, to whatever it needs to be to find the new inventory quantities. Now you select all of the entries in column C (updated values) and use Edit | Copy (or [Ctrl]+[C]) to copy the values to the clipboard. Then click in the first current inventory entry in column B, and use Edit | Paste Special and select the [Values] option on the dialog box and click [OK] - all updated values will be placed into column B, replacing the older numbers. Now on to all of the caveat's and what-about's: As soon as you close the other workbook (containing the daily update values), the entries in column C with the formulas are going to change to #REF entries. This is because INDIRECT() doesn't work unless the other workbook is open. So, to hide this ugliness, you could simply hide the column with the ugliness in it until the next update. Why did I use INDIRECT with the setup in cell D1? Because as I said, and as you indicated, the update lists may vary from day to day. When you get a new update workbook each day, you can make edits in cell D1 to quickly change the name of the workbook, the sheet the updates are on and the range where the update information is at. You would first open up the other book along with the inventory workbook and make the edits in cell D1 and the values in column C should change from #REF to actual values as soon as you exit from cell D1. Suggestion: since I suspect that the list if items in the to-be-updated workbook will be fairly static, you could start recording a macro at the point where you start to select the entries in column C with the new inventory quantities in them. Then stop recording it after you get the values pasted into the proper column. Then each day you could simply: Open the new workbook with the updated inventory list Change the reference in D1 as required Run the macro you recorded to move the values from the formula column into the actual data column. Two minutes of effort and you're done. If you need any more detailed help with this or if I've confused you to no end, feel free to contact me at (remove spaces) HelpFrom @ jlathamsite.com I know it was a long read, with some complex instructions, but I believe it will provide you with a long-lasting, very flexible solution. "Stephen" wrote: I have worksheet that has a list of my inventory. On a day to day basisi i download a new file that has a complete list of inventory for my wholesaler. What i am trying to do is take that data from my wholesaler find the products that i am intrested in which will then automatically update any inventory changes to the products qantity. So to sumerize i need to Find specific data from one file compare that to my exisiting file and update one cells values depending on if there are changes. Currently i am going line by line using the find fuction from one book to find the data in the other book then looking to see if values have changed and updating as needed. This is very time consuming. Any help on this would be great. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro | Excel Discussion (Misc queries) | |||
how to automatically update inventory list with sales | New Users to Excel | |||
update prices in inventory based on downloaded price list?? | Excel Worksheet Functions | |||
Software Update INVENTORY Query | Excel Worksheet Functions | |||
Prompt to update links | Links and Linking in Excel |