Create a summary page that self populates only if a value is entered on one of 9 sheets

Hi everyone,
I need to create a summary page of items and order quantities based on size which pulls from a master inventory list in the same workbook.
That master inventory list needs to pull whatever has been entered in a specific cell based on item number and size. This inventory list does not have available inventory or anything like that.

I cant seem to figure out the best way to create these formulas without getting them overly complicated or convoluted.

I need to find a way to make this simple enough that I can change out the item numbers and sizes without having to re-formula the whole thing every year.

Currently there are multiple sheets set up in the same way with the item number in the left hand column, a description in the second column, followed by varying headings of sizes (XS, S, M, 9, 10, 11, etc) depending on the category with a spot for the sales rep to put in what the customer has on hand, and what they think that the customer should order.

I need to pull whatever values are entered and relate them to the item number that is size specific. (hence the master inventory list)

Then I need to pull whatever items on the master inventory list that have a value into a concise one page report that has the item number with size and order quantity.

Here is the file, I hope that it works.

replen worksheet.xlsm

I don't have the summary page yet put in as well I have no idea how to go about it yet. If I could at least get the master inventory page figured out then I would be able to probably just return non blank cells or something like that.

Any help is appreciated.
(I am aware that there are actual programs out there that do this, but for some reason I was asked to do this for my boss despite us actually having the programs that already do something like this).

I hope these are adequate answers. I'm trying hard to give as much information as possible without being overwhelming and without expecting someone to just do the spreadsheet for me.

Orders are entered on the individual sheets (i.e.: Helmets, Boots, etc.)?

Yes orders are entered on the individual sheets. You have two columns for each size to enter in what they have for On Hand inventory (O/H), and what the sales rep thinks they should order (OR).

The master inventory list that you refer to, is that the Master SKU tab?

Master inventory list is Master sku tab, but i figured master inventory was more global in terminology.

So you want data summarized to that from the individual sheets?

I want the data from the category sheets (boots, helmets, etc) summarized to that tab from the individual sheets so that i can reference the entire list and populate a quick summary for sales reps to see. It would be three columns, sku (including size) description, and recommended order qty.

But the Master SKU doesn't seem to differentiate by size.

So master sku does differentiate by size, under the sku heading. it goes material number - color - size so like 11111-222-XS. Hence why it looks like there are double entries on some things. Sorry there are a bunch of items at the top that only have one size with the OS designation. Sorry I should have clarified that.

Do you have an idea of what the summary sheet would look like?

Ideally similar to an invoice, but mostly it just needs to have three columns that show sku with size included, description, and qty recommended to order.
I can play around with the major aesthetic pieces of it later on if I can learn the meat and potatoes of what I would need to be doing.

Please let me know if there is any other information that would be helpful.