Tracking Uniform

[Heato]

I would like to create an excel sheet that allows me to easily keep track of uniform stock and distribution. I do not need to track expenses, just current stock leavels and issuing of stock. I have read through other examples, but have not been able to find something that is automated. I would like for example, select shirt 1 and sizes come up, select Shoes and a different list of sizes come up. I would then like to automate it so a full stock list is calculated and updated automatically.

Issues are, i have multiple shirts in different sizes:

Shirt 1
Shirt 2
Shirt 3

Sizes
XS
S
M
L
XL
XXL
XXXL
XXXXL

I have Pants in different sizes:

Pants 1
Pants 2
Pants 3

28
30
32
34
36
38
40
42
44


I have Shoes in different sizes:

Shoes 1
Shoes 2

5
5.5
6
6.5
7
7.5
8
8.5
9
9.5
10
10.5
11
11.5
12
12.5
13

I also have Jumpers in different sizes

Jumper 1
Jumper 2

XS
S
M
L
XL
XXL
XXXL
XXXXL

And i have others items like Ties / Scarfs with No size


How can i make a simple stock tracjing sheet that will allow me to easily complete a stock take of my current inventory and then a Tab for issed uniforms and a tab to track stock levels and highlight low stocks.

Any assistance would be greatly appreciated.

Thank you
Heato

 

[shamimarita]

Hello Heato,
Welcome to the forum! Your uniform tracking setup is a very common need (schools, security companies, factories, etc.), and Excel can handle it quite nicely with dependent dropdowns, simple formulas, and conditional formatting for low stock alerts.
Here’s a practical approach to build what you’re describing, no VBA needed unless you want extra automation later.
Suggested Workbook Structure (3–4 sheets)
Items & Sizes (reference/setup sheet – hidden if you want)

• List all your item categories and their valid sizes.

Example layout (you can copy-paste this structure):

Category	Item	SizeListName
Shirt	Shirt 1	ShirtSizes
Shirt	Shirt 2	ShirtSizes
Shirt	Shirt 3	ShirtSizes
Pants	Pants 1	PantSizes
...	...	...
Shoes	Shoes 1	ShoeSizes
NoSize	Tie	(blank or "None")
NoSize	Scarf	(blank or "None")

Then in the same sheet (or a separate one), define the actual size lists using named ranges:

• ShirtSizes: XS,S,M,L,XL,XXL,XXXL,XXXXL
• PantSizes: 28,30,32,34,36,38,40,42,44
• ShoeSizes: 5,5.5,6,6.5,7,7.5,8,8.5,9,9.5,10,10.5,11,11.5,12,12.5,13
• JumperSizes: same as shirts

(Use Formulas → Name Manager to create these named ranges.)
Stock Take / Current Inventory (main stock sheet)

• Columns: Item, Size, Current Stock (number), Minimum Stock Level, Status
• Use Data Validation + VLOOKUP / INDEX+MATCH / XLOOKUP for dependent dropdowns.
• In A2: Data Validation → List → source = unique category list (Shirt, Pants, Shoes, Jumper, NoSize)
• In B2 (Item dropdown): Data Validation → List → source = =FILTER(Items_Sizes[Item], Items_Sizes[Category]=A2)
• In C2 (Size dropdown): Data Validation → List → source = =IFERROR(FILTER(INDIRECT(VLOOKUP(B2,Items_Sizes[[Item]:[SizeListName]],3,FALSE)),"") (or use XLOOKUP if you have newer Excel: =XLOOKUP(B2,Items_Sizes[Item],Items_Sizes[SizeListName],""))

Then manually (or via Power Query later) populate initial stock quantities.
Issued Uniforms (transaction log – best for history)

• Columns: Date, Employee Name/ID, Category, Item, Size, Quantity Issued (-number), Notes
• This acts like your outgoing record.

Stock Dashboard / Levels (summary – auto-updates)

• Use a Pivot Table from the Issued log + initial stock, or formulas like: Current Stock = Initial Stock + SUMIFS(Issued[Quantity], Issued[Item], A2, Issued[Size], B2)

Add conditional formatting:

• Rule: Cell Value ≤ Minimum Stock Level → red fill + bold text
• Or use icon sets (traffic lights) for visual low-stock alerts.

 

[JORGE W: ROSERO]

Me gustaría crear una hoja de Excel que me permita controlar fácilmente el inventario y la distribución de uniformes. No necesito controlar los gastos, solo los saldos de existencias actuales y su salida. He consultado otros ejemplos, pero no he encontrado nada automatizado. Por ejemplo, al seleccionar la camisa 1, me gustaría que aparecieran las tallas; al seleccionar Zapatos, que apareciera una lista diferente de tallas. Luego, me gustaría automatizarlo para que se calculara y actualizara automáticamente una lista completa de existencias.

El problema es que tengo varias camisetas de diferentes tallas:

Camisa 1
Camisa 2
Camisa 3

Tallas
XS
S
METRO
L
SG
XXL
XXXL
XXXXL

Tengo pantalones en diferentes tallas:

Pantalones 1
Pantalones 2
Pantalones 3

28
30
32
34
36
38
40
42
44


Tengo zapatos en diferentes tallas:

Zapatos 1
Zapatos 2

5
5.5
6
6.5
7
7.5
8
8.5
9
9.5
10
10.5
11
11.5
12
12.5
13

También tengo Jumpers en diferentes tallas.

Puente 1
Saltador 2

XS
S
METRO
L
SG
XXL
XXXL
XXXXL

Y tengo otros artículos como corbatas / bufandas sin talla.


¿Cómo puedo hacer una hoja de seguimiento de existencias simple que me permita completar fácilmente un inventario de mi inventario actual y luego una pestaña para los uniformes emitidos y una pestaña para rastrear los niveles de existencias y resaltar las existencias bajas?

Cualquier ayuda sería muy apreciada.

Gracias
Calentar

Hay que identificar el producto con un código único para poder tener: Producto Cantidad Precio Stock StockVend para controlar con herramientas como subtotales y se puede tener un inventario más real y por producto.
Los codigos serían: Camisas

Camisa 1	Camisa 2	Camisa 3
Ca1XS	Ca2XS	Ca3XS
Ca1S	Ca2S	Ca3S
Ca1M	Ca2M	Ca3M
Ca1L	Ca2L	Ca3L
Ca1XL	Ca2XL	Ca3XL
Ca1XXL	Ca2XXL	Ca3XXL
Ca1XXXL	Ca2XXXL	Ca3XXXL
Ca1XXXXL	Ca2XXXXL	Ca3XXXXL

Los codigos de pantalones

Pantalón 1	Pantalón 2	Pantalón 3
PA128	PA128	PA328
PA130	PA130	PA330
PA132	PA132	PA332
PA134	PA134	PA334
PA136	PA136	PA336
PA138	PA138	PA338
PA140	PA140	PA340
PA142	PA142	PA342
PA144	PA144	PA344

Los códigos de zapatos:

Zapatos 1	Zapatos 2
ZA15	ZA25
ZA155	ZA255
ZA16	ZA26
ZA165	ZA265
ZA17	ZA27
ZA175	ZA275
ZA18	ZA28
ZA185	ZA285
ZA19	ZA29
ZA195	ZA295
ZA110	ZA210
ZA1105	ZA2105
ZA111	ZA211
ZA1115	ZA2115
ZA112	ZA212
ZA1125	ZA2125
ZA113	ZA213

Los códigos de Suéters

Suéter 1	Suéter 2
SU1XS	SU2XS
SU1S	SU2S
SU1M	SU2M
SU1L	SU2L
SU1XL	SU2XL
SU1XXL	SU2XXL
SU1XXXL	SU2XXXL
SU1XXXXL	SU2XXXXL

Bien codificado ya se podría identificar a cada uno de los productos.

 

[shamimarita]

Hello JORGE W. ROSERO,

Thank you for sharing this idea. Using unique product codes for each item and size combination is definitely a practical approach for inventory management. It helps avoid confusion and makes it much easier to track stock movements, especially when using tools like SUBTOTAL, Pivot Tables, or SUMIFS.

For example, creating codes such as Ca1M, PA134, ZA18, SU1XL, etc., allows every variation (item + size) to be treated as a unique product ID. Once each product has a code, the stock sheet can simply track:

Product Code | Item | Size | Current Stock | Issued | Remaining Stock

Then formulas like this can automatically calculate stock levels:
=InitialStock - SUMIFS(IssuedQtyRange, ProductCodeRange, A2)

This structure also makes it easier to expand the system later if more items or sizes are added. Great suggestion for making the inventory system more organized.