Inventory with Excel is it possible

[ArbiterWolf]

Hello,

I work for a place that prints and mails bills to customers for our clients. I need to keep track of envelopes and invoice paper used/sent out and what we receive.

What I have in mind might not be possible but I'm new to excel and unfamiliar with VBA so I do not know its full potential uses.

Right now I have a Excel workbook that we are using to keep track of different sorts( includes #9 envelopes, inserts, multipage, ect.) using page numbers and the total qty of everything. Thing is we open a fresh work book every time multiple times a day and do a "save as" when we are finished and save it in a folder system by month and date.

Is there a way to automate some sort of export or somehow extract the data from the multiple excel files to create a rolling count of used supplies? Could we possibly export the data to another excel file before we save it and close it? Any ideas? Would like the process to be somewhat automated if possible. I know a easy long way would be to copy and paste totals into a separate workbook to generate a rolling count of what's used but that's what I'm trying to stray away from but might revert to if there's no easier way.

Thanks in advance. Just want to pick at some more knowledgeable excel brains out there lol.

 

[Coding4Fun]

So yes it is possible, while it sounds like a job better suited for a database than an excel file you could write a macro to manage this.

If you provide specifics I could write something for you.

 

[ArbiterWolf]

I'm not opposed to a database but I have absolutely no experience with databases and I wouldn't even know what software to use or if my job would even have it installed on our computers. I could ask about it as we are upgrading to office 365 next week. But you can link a database with your excel workbooks?

This is a screenshot of the sheet the data gets input too. So we currently have 3 different places the stuff gets sent too, to get folded and mailed. So it would be nice to be able to track their inventory as well since we supply them with the envelopes, that's indicated in column I.
The main thing we need to track is #10 Envelopes and #9 Envelopes. As you can see some sorts do not get #9 envelopes. We have 4 different clients so we have 4 different #10's and 4 different #9's type of envelopes. So essentially we need something that looks at the totals in column J to determine if that total includes #9 or not Indicated in column H if its blank they get #9's. Then looks at what client in cell B4 to determine the type of envelopes they are. Then checks column I to determine what location to subtract the inventory from. Then extract this info onto a separate worksheet that can track the inventory.

Invoice paper I'm not so worried about as that is inventoried a little differently and the totals for the multi page statements on this sheet is the qty of statements not pages so it would be impossible to inventory invoices from this sheet, being multi page invoices can range anywhere between 1-30 pages.

 

[ArbiterWolf]

This would be an example of something I would need. I think the different places would have to be on different sheets/tabs but this was just a rough example I made real quick to help give an idea of what I'm looking for. I don't know if something like this is possible with excel?

 

[Coding4Fun]

In response to the Database, Office 365 comes with Access which is a database that can be used for smaller use cases such as this. It can be linked to excel yes, not exactly straight forward but it is possible.

In regards to the example you provided, I am not entirely clear on all of that...

So I understand that you have your "Input" sheet where I assume someone would be logging the data for a transaction.

I don't really understand what you mean by:
"So it would be nice to be able to track their inventory as well since we supply them with the envelopes, that's indicated in column I."

I see column I shows what appears to be a "FIM Location", I am not sure what the intent is here or what you are trying to track and where?

"The main thing we need to track is #10 Envelopes and #9 Envelopes."
You mention tracking #9 and #10 envelopes but I only see #9 (Column H) in your Input form?

"As you can see some sorts do not get #9 envelopes"
I understand you are indicating with either NO or Blank(Yes) to indicate if they get #9 envelopes or not.

"We have 4 different clients so we have 4 different #10's and 4 different #9's type of envelopes."
I don't understand what you mean by this, I get that you have 4 clients and 4 of each type of envelope but I don't see where you distinguish between them.
How would I know a version 1 #10 from a version 2 #10, same for the #9.

"So essentially we need something that looks at the totals in column J to determine if that total includes #9 or not Indicated in column H if its blank they get #9's."
This does not fully make sense, you say to look at Column J to determine if that total includes #9
- Column J appears to be the difference between Column D (Start) and Column E (End) of your input form, not sure how Column J ties into #9's
-- My understanding is Column H is used to indicate if they get #9 or not, if blank they get #9 if NO they don't get #9


"Then looks at what client in cell B4 to determine the type of envelopes they are."
Assuming there is a table or reference somewhere that links the client to envelope version.
- If client 1 use version X #10 and version Y #9 etc.

"Then checks column I to determine what location to subtract the inventory from."
I understand that Column I indicates what location to subtract inventory from, I however don't see this translation from your input for over to the "End Result"


"Then extract this info onto a separate worksheet that can track the inventory."
Understood, you want to extract the data from the "Input" form and put it some where. Not exactly clear on what data you want pulled from where and placed where. I don't see the correlation or translation from your input sheet over to the example "end result" that you provided.
- Where did the job numbers and totals come from?
- Where you indicated "Given to folding place #X" how are you determining this?

If you can clarify your request, it seems possible, I just need clear direction on what you want to copy from where to where when you "submit" the form and clear understanding of any logic rules to apply when determining the data to move where.

 

[ArbiterWolf]

Oh okay, well maybe that is something I will look into doing once we get 365 and get it setup on all our computers. Would it be worth waiting until we get access? Is it that much better? I would defiantly need help from start to finish with that. I have never used that before, but we should be getting by the end of next week hopefully.

Oh man sorry for not being more clear, I did a horrible job at explaining things. I guess when you work with it everyday it all just makes sense to me, I forget I'm talking to someone from the outside haha.

1. We fold and mail smaller batches at our location and send out bigger batches to 2 other places to be folded and mailed.

2. FIM stands for Folding inserting mailing location.....it consist of 3 Different places.

3. Everything gets a #10 Envelope and depending if the statements are requesting a return check they get a #9 envelope.

4. Cell B4 the Company would determine which client it is. Each Client has their own set of #10 and #9 envelopes they use.

5. Yes you are correct, It is just the difference between start and end columns. I was trying to say that all the totals need to be subtracted from the #10 Inventory regardless if it gets a #9 or not, but only the totals including a #9 get subtracted from the #9 inventory.

6. I do have a chart on a separate tab/sheet that references what envelopes to use with a VLOOKUP that lets the user know what envelope to use on the project sheets. But its by company name, Cell B4 would let you know what envelope is being used. Each of the 4 clients has their own set of #10 and #9 they use.

7. Yes sorry I did not do a very good job with the example page I gave, I did not reference anything from the original "INPUT" sheet. It was more of just to give you a visual idea of the type of inventory page I would need. I'm sure that probably just ended up confusing you. The totals I just made up randomly and the Job names would essentially be the Case# Cell B11 from the Input Page.
Giving the Inventory to the other places would have to be manually entered as we give them inventory as needed. We would be subtracting it from our inventory and adding it to theirs, but this is all done based on what they need any given month.

Hopefully this cleared up some stuff.
Please let me know if you have any other questions. Id be willing to share the client names with you in a PM if it will help this all make more sense.

 

[Coding4Fun]

Ok so I think I am clear on what you are after for the most part, the piece I am still unclear on is how to calculate the values.

So using the example you provided.

From your input sheet translating over to the 2nd sheet or summary...

FIM is the location which has 3 possible values, these 3 values from Input correspond with the "Folding Place" on the 2nd sheet.

->

The Case# from the Input sheet corresponds with the Job# on the 2nd sheet.

I understand everyone gets #10 and some get #9 based on column H of the Input sheet.

How do I determine what totals to carry over, how many to subtract from #10 vs #9

 

[Coding4Fun]

I think you just want to take the total from column J of the input sheet and that would be the value added to the appropriate column based on envelope type.

I think I am clear on what you are after now, as they all get #10 and only some get #9.

This example

I would have an entry put into the 2nd sheet showing that the job is being split between 2 locations FIM Location S and FIM Location Mail.

Location S would show a total of 415 # 10 envelopes and 372 #9 envelopes used for the job (76769)

Location Mail would have a total of 27,902 #10 envelopes and 25,182 #9 envelopes.

Can you supply the logic you would use to determine which envelope type to use.

 

[Coding4Fun]

Sorry talking myself through it apparently haha

I see that you have 4 clients and 4 envelope types for #9 and #10

So if Client1 then I would put the totals under Client 1 #10 and #9
Client 2 would go under Client 2 #10 and #9

 

[ArbiterWolf]

Sorry work was super busy today. I only got a chance to glance over your posts, but everything you said sounds correct. I'd say you figured it out. I'll reply tomorrow with more details. I've been meaning to make a new example sheet using relevant data. But you seem to have the idea.