Clothing Tracking Sheet Creation (out of ideas)

Hello,

I’m having trouble understanding what exactly is blocking.

It would be helpful to have some examples in your file explaining why these results.

--
Remember to say "thank you" and "solved" when the issue is resolved.

Hello,
I will create a table like this:
https://cjoint.com/c/KHtq3cEzcXA
It is locked; to test it, do not unprotect it, do not add or delete any rows or columns, only enter data in the blue cells.
This is just a draft; we can extend the table based on the actual number of employees and clothing items if it matches what you are looking for.
When the inventory clerk receives a request from an employee, they enter the reference in column B, the clothing reference in column D, the release date in column F, then they can see how much is left in stock in column K, and enter the requested quantity. The remaining stock appears in column K. In case of incorrect quantity, a signal appears.
When the employee returns the clothing, the inventory clerk enters their reference, the clothing reference, and the returned quantity on the line of the clothing's exit. It may be necessary to add a column for destroyed or lost clothing.
You will therefore always have the details of clothing stock in columns M to T.
By using the filter arrows in row 8, you will obtain the situation for one or more employees and/or one or more clothing items. It would be possible to add restocking management.
If the tests are satisfactory, fill in the Clothing and Employees databases starting from column V. Enter the initial stock in column P.
Please let me know what needs to be corrected, modified, or added.
Best regards.

Hello,
The table is protected, but without a password. I advise you not to unprotect it during testing to avoid deleting formulas; it is just a mock-up intended to "solidify ideas" and to note what needs to be modified.
Small correction: read in C8: Employee reference and in M8: Garment reference. This was a table related to the management of the tools in a factory that I have adapted as best as I could to your problem. You will see that there is a hidden column O regarding destroyed or lost equipment, but I don't know if it is well-suited to your issue.
Entries must be recorded on the same line as the exits.
I do not think that combining columns V and W with columns M and N is a simplification.
I will prepare an extended new version for you this afternoon, incorporating your garment nomenclature and without garment or employee reference.

Re,
Attached is a new version of the table.
Please test it, do not modify the formulas or the structure, but let me know what needs to be reviewed. Do not skip lines. If everything seems OK, you just need to enter the names of your employees and then explain to the person responsible for entering the data how the table works, which you can then protect with a password.
Good luck.
Best regards.
https://cjoint.com/c/KHun4iZwtmA

Hello,
I hope this table will be practical and effective to use.
As for the lost clothing, I will add an appropriate column.
Regarding who has what, you initially have the filters activated by the arrows in line 8. I will start creating a summary sheet for each employee and will send you a new version either today or tomorrow.
In the meantime, could you test the table in all possible scenarios, preferably with the employee responsible for data entry, and let me know if you encounter any issues?

Good evening,
Attached is a new version.
I took the opportunity to add the Bermuda-50 that I had forgotten and I removed an "l" from the vests.
The previous versions were created in Excel 2000, with the .xls extension, compatible with later versions. The current version is in .xlsx format, as it includes recent functions, and I therefore continued working on Excel 2016.
This table will need to be extended; if you have 50 employees to multiply by 10 items, that totals 500 rows + an indeterminate number corresponding to the frequency of renewal.
It will simply be a matter of extending the columns from Sheet1 B to K inclusive, which is not difficult.
https://cjoint.com/c/KHxr0rrbdPA

Re,
Regarding the entry of items on a different line than the exits, I just tested:
First line: Zoé, exit 1 Tshirt-XL
Second line: Zoé, entry 1 Tshirt-XL
It works, the stock correctly shows one exit and one entry and the balance is right, but I don't really like it. If it's really more practical to use this method, then go ahead. It's better not to delete column K but, for the time being, to ignore the "To be checked" signal, and later erase the corresponding formula. However, if we incorrectly record a different number of entries than exits for an employee, it will no longer be flagged.
As for the new clothing items listed in column T of Sheet 1, I have added formulas so that they automatically transfer to row 7 of Sheet 2 and entered some imaginary data for verification.
Regarding the two points that intrigued me, they do not affect the operation of the table as it is.
As for creating a recap by employee that only includes what they actually hold, that should certainly be possible, but it's not simple, at least in this structure; I need to look into it.
In the meantime, I added in Sheet 1, line 203 a function that sums the numbers extracted using the filters from line 8 of Sheet 1. And I colored the even rows of Sheet 2 to make it easier to read.
I’m sending you a new version "04 B" taking everything into account. I checked carefully, but six eyes are better than two.
That said, data entry is not the most enjoyable part of working with Excel...
Good luck.
https://cjoint.com/c/KHzo7Ri31AA

Good evening,
I am sending you a "04 C" table, raw from the foundry and not too verified, allowing for a recap by employee.
A pivot table (TCD) has been added in sheet 3. The operation is the same as with the previous tables. Then you go to the TCD sheet and if the "Refresh" signal appears in I2, you do as indicated in K6.
I will be absent until tomorrow evening. See if you can draw some ideas from this version.
https://cjoint.com/c/KHztlufTsSA

"The sheet is protected, but without a password. I recommend not unprotecting it during testing to avoid erasing formulas.

Hello,
I have been happily using "Cathy's" trick for several years now.

Select the cells with formulas to protect;
Tab: Data Validation -------------------> Custom
>=1
OK

Cathy's site: Cathyastuces.com"

@Narwe
Hello,
Is your issue resolved?
Best regards

Hello,

First of all, thank you for taking the time to look at my request.

Yoyo01000: My problem is that I have no clear idea of how to materialize my table.

- I need to know what clothing each employee has (type and size) and what I have in stock.

In my table, I could see what each employee had, but only in terms of the number of a type (T-shirt, jacket, etc., in cells B5; B7; B9….) and not by size (cells B6; B8; B10….) because it only displays one size, and more importantly, how I can use this data later for stock tracking.

In light of Eugene-92's proposal, I understand better why my approach couldn't work.

Eugene-92: Thank you very much; with this approach, we are really close to the result we want to achieve.
Indeed, a "non-reusable" and "not returned" column (which would have the same impact on the stock) needs to be added.

Managing restocking would be nice, but it's really a luxury. In itself, it isn't difficult for us to calculate how many pieces of clothing an employee is missing when we know what they have left.
To make it easier for them, I think dropdown lists with the clothing and employees would be simpler.

My clothing references are:

SWEAT; TSHIRT; JACKET; VESTS; YELLOW VESTS in sizes M; XL; 2XL; 3XL; 4XL
BERMUDAS; PANTS in sizes 38, 40, 42, 44, 46, 48, 50, 52, 54
SHOES: 38, 40, 42, 44, 46, 48
HELMET: One size

The protection of the sheet prevents me from entering references after pants 42 and modifying the reference codes.

https://www.cjoint.com/c/KHujbsmiL61

It is possible that other references will be added over time.

And there are about 40-50 employees who are likely to wear clothing (I will replace the names in the final version).
Quick question: can't we combine columns V and W with columns M and N?

Thank you very much for your insight and help on the subject.

Hello Eugene-92,

Thank you very much for this table, it's really helping me a lot :)

Everything looks great and should allow us to track inventory easily (we'll see how it performs in use).

For the "destroyed" column, I was thinking of placing it after column G "quantity received," in order to account for it during returns and avoid deleting a value that's already recorded. I'll see if I can easily achieve this with an "if" and "sum" function on my end.

May I ask for your opinion on the difficulty of adding a summary table of donated clothing on sheet 2, allowing us to easily see who has what and in how many copies?

In any case, thank you very much for your help.

Yes, I will show him and I will give you the feedback he gives me. Thank you.

Hello,

Thank you for all these modifications,

My colleague rightly pointed out that it will be tedious to link the entries to the exits, as we will have to find them. In my opinion, the simplest way is to simply write in one line when something goes out and in another when something comes in.
This avoids having to search in the table for where the original reference was, and the stock tracking remains good nonetheless.

There is nothing to change per se; we just need to disregard (or remove) column K.

We also noticed that when a garment is added in column T of sheet 1, it does not appear in row 7 of sheet 2.

Although the table in sheet 2 helps us keep track, my colleague asked if it would be possible to have a recap presented like this:
- Bertrand: 3 XL bermudas
 2 XL T-shirts
 1 TU helmet
 1 2XL sweatshirt

To avoid searching in the table (and making a mistake with the wrong line). Personally, I believe that what you have done is already sufficient to warrant this request, but I am sharing all these observations with you.

Here is the answer to your intriguing points:

- We do sometimes provide different sizes to the same person because one size is out of stock, so we complete with a larger size. It also happens that staff change sizes due to weight loss, in which case the employee generally keeps their old outfits and reuses them without returning them to us (we still consider them as being in the employee's possession in this case) they should return them to us.

- That's correct, returned and undamaged clothing is reintegrated into the stock to be reused (after washing, of course). If the clothing is unusable (damaged or lost), we remove it from the stock.

Thanks again for your help; it is very useful to us.

The message shows 19 replies, but only 14 are displayed.

Hello Eugene-92,

Sorry for this late response due to some unforeseen circumstances here.

I think we're good (especially you actually), we will use this solution which is simply perfect. We'll see if we find anything to improve in its use.

I greatly appreciate your help and knowledge. They will really allow us to tackle this subject effectively.

Thanks again

Narwe

I opened the example file; and there is something I didn't understand about how it works
There is a balance column and a new balance column after the transaction.
In the case of a new entry, the old balance becomes the new balance. How does the operation work?

Hello,
I am a bit late to share my "ideas" or rather my suggestions with you.
In the main sheet for entering transactions, I have created the following columns!
No (optional), Date, Beneficiary Code, Beneficiaries, Article Code, Description, Delivered, Returns, P/U, Stocks
P = Lost, U = Used, to be taken out of stock.
I have created an Article sheet with a Code column and another for Description:
I kept your description but I created a coding for the article code:
1st character, the family!
1, Sweatshirt
2, T-shirt
3, Coat
4, Vest
5, Yellow Vest
6, Bermuda Shorts
7, Pants
2nd and 3rd characters are for the size.
01, M
02, L
03, XL
04, 2XL
05, 3XL
06, 4XL
38, 38
40, 40
42, 42
44, 44
56, 46
48, 48
50, 50
52, 52
54, 54
Thus, the code 503 will correspond to a Yellow Vest in size XL.

Please note that in the lookup formulas you must perform an exact match, for that you need to set the default value to FALSE.
Best regards.