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.

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.

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.