If/then cascade effect for inventory spreadsheet.

[Holmes89]

Looking for an if/then formula that includes a cascade effect. For example column A is pallets of beer, it holds 24 cases of 24 beers. Column b is cases (so when you open a pallet you then 24 cases) then column c has individual bottles. Once column C goes into the - (minus) I would like the spread sheet to drop from 24 cases to 23 and the individual bottles and pallet to self adjust.
Fairly new to excel looking to impress my boss

 

[Joe4]

How exactly is column C "going in to the minus"?
Is someone manually updating column C, or is there a formula in column C?

I think it would be helpful to see a sampling of your data along with an explanation of:
- which columns are hard-coded and which are formulas
- how it is getting updated (exactly which columns are being manually updated)

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Holmes89]

Someone is manually updating the inventory but my boss wants it to auto populate after they stock what they need.

 

[Joe4]

Someone is manually updating the inventory but my boss wants it to auto populate after they stock what they need.

That does not answer any of my questions.
If you want us to help you, we need the details! It is very hard to program to generalities.
Remember, while you have the data in front of your and are familiar with how everything looks, we are not. The only thing we have is what you share with us on this thread.

Please show us an example of the data like I asked and answer the questions in detail.

 

[Holmes89]

That does not answer any of my questions.
If you want us to help you, we need the details! It is very hard to program to generalities.
Remember, while you have the data in front of your and are familiar with how everything looks, we are not. The only thing we have is what you share with us on this thread.

Please show us an example of the data like I asked and answer the questions in detail.

My apologies and thank you for any time you take to help me with this. I posted a screen shot of the sheet, I cannot download the program as its a work computer. I'm looking to manually enter how much of the stock we use on the right side of the sheet. I would like one of my colleagues to be able to put -24 bottles of Heineken as we take 24 from the store room and put them on the floor. If I do not have enough individual bottles for that I would like column E to be -4 (6 packs) then column F would then go +24. As I would like a cascading effect if I do not have enough 6 packs then a case in column D would get broken apart so -1 cases, then + 4 (6 packs). I would like this effect to happen any time any of the columns on the right side of the spread sheet go into the negative.

 

[Holmes89]

That does not answer any of my questions.
If you want us to help you, we need the details! It is very hard to program to generalities.
Remember, while you have the data in front of your and are familiar with how everything looks, we are not. The only thing we have is what you share with us on this thread.

Please show us an example of the data like I asked and answer the questions in detail.

 

[Joe4]

Are there any formulas anywhere on the sheet currently?
If so, what cells are they in and what are they exactly?

Can you walk us through an example step-by-step, denoting the exact values being places in the exact cell address, and then exactly what cells should be reflected (changed and to what values)?
I want EXACT amounts and cell addresses for each step of the way.

Note that if there are no formulas currently on the sheet, but you want values to be automatically adjusted, the only way to do that is with VBA code.
Are you open to that? I know many companies might not allow that.

By the way, inventory stuff is usually best done in relational database programs, like Microsoft Access. If you do a Google search on "microsoft access inventory template", you can find many ready-made templates people already created to track inventory.

 

[Holmes89]

Are there any formulas anywhere on the sheet currently?
If so, what cells are they in and what are they exactly?

Can you walk us through an example step-by-step, denoting the exact values being places in the exact cell address, and then exactly what cells should be reflected (changed and to what values)?
I want EXACT amounts and cell addresses for each step of the way.

Note that if there are no formulas currently on the sheet, but you want values to be automatically adjusted, the only way to do that is with VBA code.
Are you open to that? I know many companies might not allow that.

By the way, inventory stuff is usually best done in relational database programs, like Microsoft Access. If you do a Google search on "microsoft access inventory template", you can find many ready-made templates people already created to track inventory.

Unfortunately I tried to talk my boss into changing the program but she's keen on using this spreadsheet (smh)

There are simple formulas across the sheet. Under Milwaukee beer, Column P7 has an =D7, Column Q7 has an =E7-H7 and then R7 has an =F7-I7, they all follow the same equations for all the different beers.

On I7 I have 24 individual bottles being brought from the inventory into the front room. Under F7 I only have 15, therefore I need to Change E7 to -1 as I only have 1 6 pack. I will need 2 to fill the order. Therefore a case (D7) would get broken down. D7 becomes 0, E7 becomes 5 but -2 to fill the individual order of 24. I would then have 3 individual bottles (F7) 3 6Packs E7 and 0 Cases.

She wants to find a way so when I add it to the inventory removal section it auto updates those 3 categories based on what were removing from the inventory.

I uploaded a new image with different inventory to be able to explain how she wants it to work.

 

[Joe4]

I am not sure why you have "6 packs" on one side and "12 packs" on the other. I am guessing maybe that is a typo?

In any event, this seems like it could get rather complex to me, with the various combinations and checks, even if trying to use VBA. I don't think I have the time to commit to trying to figure something like this out. It might be a bit much to expect from "free help" and may fall into more of the "Consulting" category.

Who knows, maybe someone else has already done something like this before, or has some clever way of figuring it out.

 

[Holmes89]

I am not sure why you have "6 packs" on one side and "12 packs" on the other. I am guessing maybe that is a typo?

In any event, this seems like it could get rather complex to me, with the various combinations and checks, even if trying to use VBA. I don't think I have the time to commit to trying to figure something like this out. It might be a bit much to expect from "free help" and may fall into more of the "Consulting" category.

Who knows, maybe someone else has already done something like this before, or has some clever way of figuring it out.

ok, thank you for your time anyway. appreciate it.