ArvLaanemts


On the inventory sheet under Part Number where you have XXXXX and YYYYY it will not let me put in a part number. I get the following error.
The value you entered is not valid.
A user has restricted values that can be entered into this cell. So if I want to put in 9N2360 it gives me the error.

Melvin1942

You can enter (or select from Data Validation dropdown) only parts, registered on Parts sheet.

Same for inventory dates - they are registered on Inventories sheet.

This is for avoiding typing errors when entering data into Inventory sheet.

Could you walk me through how to enter parts and extend them on down? Same with the qty to order. Do I copy them?
I like what you have do. I just need to learn how to use it.

melvin1942

To register new parts:

On Parts sheet, either:
1. Activate bottom right cell of Table tParts, and then press Tab to add a new row to table. Into new row enter part number and quantity to keep for this part;
2. Activate any cell in Table tParts. From Menu select Design, and then at left of menu pane click on <Resize Table button>. In Resize Table popup window set new table range (edit the row number, and press <OK>;
3. Simply enter new part number into next row immediately below last row of Table.

Quantity to order is calculated automatically in table. NB! In my table is error in formula for QtyToOrder! The right formula is
You enter the correct formula into any cell in Table field, and it is corrected for whole field.

When you enter the QtyOnHand after inventory into Table tInventory, the quantity needed to increase current quantity to QtyToKeep is calculated in field QtyToOrder. When you purchase this part and enter purchased quantity into field QtyPurchased, the QtyToOrder is decreased accordingly (but never gets negative).

All formulas/data validations/formats are copied automatically into new rows of tables, so long as they remain same for whole column.

On PartOrder sheet you select inventory date, which serves as ID for inventory, and all parts with QtyToOrder > 0 for this inventory are listed in table. You have to decide yourself, how much different parts you'll have, and set the number of Table tPartOrder accordingly (my advice is, multiply the number of parts you think you need with 2 or 3). Btw, the easiest way to expand the table is select all table sells in last row, and drag them down.

The inventory sheet can hold many inventories (you need a new table or to delete old inventories after the number of rows grows over 20000 or even more). Before enter new inventory data into tInventory (there is spelling error in my file, correct tInventoty to tInventory), you have to register the new inventory date in Table tInventories on sheet Inventories.

Arvilaanemets, I am completely lost. I went in under Qty to Order on the inventory sheet to change the formula that you showed in you last message. For some reason it did not like what I put in. Now I cannot get it to take any changes that I put in. Maybe I am in the wrong sheet. Could you send me the file again? I do not see it anymore to be able to re download it. After I get it I have other questions. I am not understanding what I am looking at. Thank you very much.

Melvin1942

Here you go! (I corrected the formula!)

Attached Files

Inventory.xlsx (22.1 KB, 10 views)

I hate to sound stupid but what is Table tParts? Do you need to put (Part Number) and (Parts to keep) in at this time, or can you do all your information at one time? When you resize you say to (edit the row number) do you put a row number in (Order Row) at this time? I have never done any of this before so please be patient with me, I do appreciate what you are doing for me.

Melvin1942

On sheet Parts is a table, where you register parts. This table is defined as Table tParts (select any cell in table and select Design from Menu - in Table Name field in Menu Ribbon you see the name of defined Table). Here you enter all parts you have, and quantities to keep for those parts. When afterwards you get new parts to account for, you add those into tParts. When the quantity to keep changes, you edit it (as this is a very simple application, you can't keep e.g. the history of quantities to keep here). When you don't need some part anymore, you can delete according row from table, or you can set QtyToKeep = 0.

Below Table Name field in Menu ribbon is a button <Resize Table>, which opens a "Resize Table" popup window. There is a field, where you can edit data range of Table. For tParts, in my example there the data range is "=$A$1:$B$4". When you edit it to "=$A$1:$B$14", and press <OK>, 10 new empty rows are added at bottom of Table tParts.
You also can simply enter a new PartNumber into cell A5 - the Table TPards is expanded for one row.
Or you can select cell B4 (the bottom right cell of tParts, and press Tab - an new empty row is added at bottom of tParts.

The same applies for all other defined Tables (tInventories, tInventoty, and tPartOrder). Btw, I tried to remember yesterday, what more to correct, and didn't remember to correct Table name (tInvetoty > tInventory).

On sheet PartOrder, you can only select an inventory date, and resize the Table tPartOrder, when the number of rows in Table is not enough to display all parts you have to order (when there aren't any empty rows at bottom of table, then certainly resize the table). Never enter anything into tPartOrder. When you do this accidently, then select table cells from some row not corrupted, and drag the corrupted row over to restore formulas.