Hello all.
I'm new to excel but I have learned a lot from YouTube videos and other resources so I am not a complete newbie. Excel is used heavily at my new job and I want to be as knowledgeable as I can to make my job easier.
At my job we have an inventory program called MAS. It exports excel inventory reports I use for scheduling shipments in and out of our warehouse. The report I am allowed to access is functional but the other functions of MAS are cumbersome and just wastes a lot of time waiting for it to load information for single orders or products. So I started dumping the inventory report into an excel worksheet and set up a look up sheet to to search with. This is 100 times faster and more productive.
I've been struggling with this problem for a while and I can't seem to find a solution. This could be because I don't know the technical terms for what I am trying to do. So I will try to be as clear as possible.
The report is set up by the different products we sell. It lists the product and then gives all the orders associated with that product. All the information I need is in the same row as order number except for the total quantity we have in stock and the product its self. I'm adding a rough sample below.
All the information I need is associated with the PO#. What I want to do is be able to look up the PO# and return the item and the Warehouse Total.
Any help you can give me would be awesome.
I'm using Excel 2010 at home and work. Also the IT guys for my company will not allow add ins and frowns upon VB. So a formula would be best. If it's not possible with a formula I can try to talk the IT guys into another option. I have not posted this question anywhere else as I was told this was the place to be if I had any questions.
<tbody>
</tbody>
I have been using the "Find & Select" blanks and copying down the rows to get around this problem but trying to teach my boss this technique so he can do it when I'm on vacation or off on the weekends is nearly impossible. It would be nice just to be able to run the inventory report and dump it on a sheet in the workbook.
Thanks everyone.
I'm new to excel but I have learned a lot from YouTube videos and other resources so I am not a complete newbie. Excel is used heavily at my new job and I want to be as knowledgeable as I can to make my job easier.
At my job we have an inventory program called MAS. It exports excel inventory reports I use for scheduling shipments in and out of our warehouse. The report I am allowed to access is functional but the other functions of MAS are cumbersome and just wastes a lot of time waiting for it to load information for single orders or products. So I started dumping the inventory report into an excel worksheet and set up a look up sheet to to search with. This is 100 times faster and more productive.
I've been struggling with this problem for a while and I can't seem to find a solution. This could be because I don't know the technical terms for what I am trying to do. So I will try to be as clear as possible.
The report is set up by the different products we sell. It lists the product and then gives all the orders associated with that product. All the information I need is in the same row as order number except for the total quantity we have in stock and the product its self. I'm adding a rough sample below.
All the information I need is associated with the PO#. What I want to do is be able to look up the PO# and return the item and the Warehouse Total.
Any help you can give me would be awesome.
I'm using Excel 2010 at home and work. Also the IT guys for my company will not allow add ins and frowns upon VB. So a formula would be best. If it's not possible with a formula I can try to talk the IT guys into another option. I have not posted this question anywhere else as I was told this was the place to be if I had any questions.
| Open Orders | ||||||
| item | Warehouse Total | |||||
| Toothpaste | 300 | |||||
| PO# | Qty On Order | |||||
| 0001000 | 20 | |||||
| 0001001 | 20 | |||||
| 0001002 | 20 | |||||
| 0001003 | 20 | |||||
| 0001004 | 20 | |||||
| item | Warehouse Total | |||||
| Hand Soap | 535 | |||||
| 0001005 | 60 | |||||
| 0001006 | 60 | |||||
| item | Warehouse Total | |||||
| Lotion | 901 | |||||
| 0001007 | 35 | |||||
| 0001008 | 35 | |||||
| 0001009 | 35 | |||||
| 0001010 | 35 | |||||
| 0001011 | 35 | |||||
| 0001012 | 35 | |||||
| 0001013 | 35 | |||||
| item | Warehouse Total | |||||
| Shampoo | 300 | |||||
| 0001014 | 20 | |||||
| 0001015 | 40 | |||||
| 0001016 | 90 | |||||
| 0001017 | 10 | |||||
| 0001018 | 15 |
<tbody>
</tbody>
I have been using the "Find & Select" blanks and copying down the rows to get around this problem but trying to teach my boss this technique so he can do it when I'm on vacation or off on the weekends is nearly impossible. It would be nice just to be able to run the inventory report and dump it on a sheet in the workbook.
Thanks everyone.
