Excel Help with Stock Control/Sales/Re-ordering

Hello, I am looking to get help, because this is a tricky one. So I have this spreadsheet and because our business is not automated in any ways we always have to enter data manually. So what I have already did I made 3 sheet workbook and I want it to be like following:

Sheet1 - input the item you sold as you go ((its like up to 10 items a day)(Need help here)
Sheet2 - automatically gives you result what to order (I have VB macros to sort, everything seems working fine here)
Sheet3 - item list + stock list (all the formulas seem to be ok, everything works. Need to still link it to "Sheet1".

So what I am trying to do, because here in our business there is old people working and they are looking to make it really simple. So in "Sheet1" if they enter HP 10 I want one to be removed from stock and then if stock is lower than reorder point it will automatically goes to "Sheet2" to reorder items. At the end of the day I can see what needs to be ordered in "Sheet2"

I have finished everything apart from "Sheet1" I can't figure it out what do I do from here. Do you use index and match or macro or what?

I would like so whenever person tries to type "HP 22 TRI-COLOUR" he won’t need to be 100% accurate only "HP 22 TRI" will work. Just to make it simple for older people in work here. So whenever someone enters HP 22 TRI it will remove 1 from the stock in Sheet2
There is spreadsheet I have:

InventoryControl.xlsm
b0a1aab6ed7c91c1a8168da0ce5c7767.png

Originally Posted by JohnTopley

You will need to use VBA macro to decrement your "Quantity in Stock" level (column G).

You might consider using the attached technique for Sheet1 (Selecting Order items)

Thanks I think that is what I was looking for. Now I am just looking how to implement this in my current workbook. Any help appreciated.
This is my latest document (Macro enabled)
InventoryControl (1).xlsm

A "prototype" for your order process:

Code in "Sold Items";

Right click on tab, "View Code" ==> copy/paste code

Please Login or Register  to view this content.

Code in a general module

Please Login or Register  to view this content.

I created a dynamic named range called "Products"

Refers to: =OFFSET('Inventory List'!$D$5,,,COUNTA('Inventory List'!$D:$D),1)

In "Sold Items" column A I have a Data Validation based on "Products"

On selection of quantity, the "Check_Inventory" module is called from the "Worksheet_Change" in "Sold Items"

If there is insufficient inventory for the order, a message is place in column C

Changed "Order" tab to "Re-Order"

Originally Posted by JohnTopley

A "prototype" for your order process:

Code in "Sold Items";

Right click on tab, "View Code" ==> copy/paste code

Please Login or Register  to view this content.

Code in a general module

Please Login or Register  to view this content.

I created a dynamic named range called "Products"

Refers to: =OFFSET('Inventory List'!$D$5,,,COUNTA('Inventory List'!$D:$D),1)

In "Sold Items" column A I have a Data Validation based on "Products"

On selection of quantity, the "Check_Inventory" module is called from the "Worksheet_Change" in "Sold Items"

If there is insufficient inventory for the order, a message is place in column C

Changed "Order" tab to "Re-Order"

I really appreciate your time and help.

I've tried using your prototype. But the problem I get is when I am inputting there is no feedback from excel. I mean it wont give me any suggestions and instead of HP 10 BLACK I can leave HP 10. I just want it to auto fill and wont allow me to misspell anything.

And if possible, as soon as I input something in "Sold Items" would you be able to make it so it would automatically subtract one from inventory therefore it will automatically put me what to re-order on "Re Order" sheet. I don't know if this is possible I would like to have a similar drop list with suggestion and whenever you press enter it would automatically subtract one from inventory and also disappear after pressing enter.

I want to leave it simple. I want to have only one input in this sheet. That is what have been sold everything else would be automatic. (But I want to keep stock lists and everything).

Post a ample rather leave a blank sheet for us to "guess" what you want.

Shop scans a product for a sale it goes automatically to their system to reorder and changes stock levels accordingly and at the end of the day they have a list of things to reorder so they place an order at the end of each day (Benefit of that, is that they can see how much stock is missing, what is popular etc...)

As I have stated before this is what "sold items" does: and the "re-order" sheet lists all items where new stock is required.

I want sheet 1 "Sold Items" to be input for other sheets to automatically work

What other sheets? Please give a full explanation of your requirements.


Do the following with attached "InventoryControl"

in "Re_order" sheet HP 21 XL is shown as requiring re-ordering

In "Inventory List" change HP 21 XL Stock qty to 10

Go back to "Re-Order": HP 21 XL is no longer on the list,

Go to "Sold Items"

Select "HP 21 XL" with Qty 8

Go to "Inventory List": HP 21 XL .... Stock Qty =2



The attached shows how have a "searchable" drop down.

Originally Posted by JohnTopley

You need to apply the "searchable" drop down logic: the current drop down does not have this and only allows to type in a FULL description.

I have tried applying the searchable drop down logic, but by doing so worksheet one does not work as same.

Search-Suggestion.gif

This is what I am trying to achieve.