This was a question I asked a while back and got some great advice.
Background:
I run a few online retail stores that ship products from various 3rd party warehouses. These outside warehouses send me weekly quicbooks inventory reports output as excel files.
My goal was to track changes over time so I could spot items about to sell out and items that were back in stock.
Mr. Excel suggested I do this in Access and I have been doing just that for the last few months. I get the external data via excel spreadsheet, create a report using the wizard and add weekly data for abou 2 months worth of data. I then conditionally color format the fields to show items running low, or items back in stock. Works pretty good except for the fact that I need to manually import the data and re-create a report every week.
Question -
There are only 3 fields in the inventory report that I am intersted in when it gets sent to me as an excel file.
Item #, desctiption, available units.
The only field that changes over time are the available units.
Isn't there an easier way of compiling and analyzing this data with just excel?
Speficially - let's say I have this weeks data in 3 columns. I get next weeks data. What I want to do is to add a new column with the date, but make sure that the available units match the item#. Is there a way to import this column and make sure the data coincides with the item #?
e.g.
Item # | description | available units | available units week 2 etc. etc.
03454 | green lure | 300 | 250
Goal is to add a new column of inventory data every week quickly.
Any suggestions would be appreciated.
Thank you
Background:
I run a few online retail stores that ship products from various 3rd party warehouses. These outside warehouses send me weekly quicbooks inventory reports output as excel files.
My goal was to track changes over time so I could spot items about to sell out and items that were back in stock.
Mr. Excel suggested I do this in Access and I have been doing just that for the last few months. I get the external data via excel spreadsheet, create a report using the wizard and add weekly data for abou 2 months worth of data. I then conditionally color format the fields to show items running low, or items back in stock. Works pretty good except for the fact that I need to manually import the data and re-create a report every week.
Question -
There are only 3 fields in the inventory report that I am intersted in when it gets sent to me as an excel file.
Item #, desctiption, available units.
The only field that changes over time are the available units.
Isn't there an easier way of compiling and analyzing this data with just excel?
Speficially - let's say I have this weeks data in 3 columns. I get next weeks data. What I want to do is to add a new column with the date, but make sure that the available units match the item#. Is there a way to import this column and make sure the data coincides with the item #?
e.g.
Item # | description | available units | available units week 2 etc. etc.
03454 | green lure | 300 | 250
Goal is to add a new column of inventory data every week quickly.
Any suggestions would be appreciated.
Thank you