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

 

[dreid1011]

I would be interested in playing around with this. Is the screenshot the entire sheet? And all manual entries are done on the right side you said? Please tell me exactly where all manual entries will be made.

 

[Holmes89]

I would be interested in playing around with this. Is the screenshot the entire sheet? And all manual entries are done on the right side you said? Please tell me exactly where all manual entries will be made.

the manual entries are under inventory removal, the right side of the sheet is just the total count of the starting inventory. Yes, this is the entire sheet.

 

[Holmes89]

I would be interested in playing around with this. Is the screenshot the entire sheet? And all manual entries are done on the right side you said? Please tell me exactly where all manual entries will be made.

under inventory removal, if they need to stock 24 bottles they would manually input 24 into individual and if they're stocking packs or cases they would manually add that number as well.

 

[dreid1011]

the manual entries are under inventory removal, the right side of the sheet is just the total count of the starting inventory. Yes, this is the entire sheet.

I don't see anything on the sheet labeled inventory removal. You'll need to be as exact as possible about where the entries are made, as in, which column/rows? Assume I know absolutely nothing about your sheet and what you want to achieve except for the image you have provided. We're going to go through this step by step to help do this as efficiently as possible. So, if you could, please explain how to use this sheet as if you were training me to do the job, and I would like to know the pallet/case/bottles breakdown. You mentioned it in your first post, but it doesn't quite line up with the image.

 

[Joe4]

You are welcome.

I don't see anything on the sheet labeled inventory removal.

It wasn't on their first image, but was on their second one (they made some changes between the two):

 

[Holmes89]

I don't see anything on the sheet labeled inventory removal. You'll need to be as exact as possible about where the entries are made, as in, which column/rows? Assume I know absolutely nothing about your sheet and what you want to achieve except for the image you have provided. We're going to go through this step by step to help do this as efficiently as possible. So, if you could, please explain how to use this sheet as if you were training me to do the job, and I would like to know the pallet/case/bottles breakdown. You mentioned it in your first post, but it doesn't quite line up with the image.

OK, so H7 all the way to H18 as well as I7 to I18 will all be entered manually. Once entered manually I would like it to auto-calculate D7-D18, E7-E18 as well as F7-F18. Here's the tricky part, I would like the D column to have a value of 24 (as per 24 beers per case) The E column to have a value of 6 (4-6 packs per case as far as column D is concerned) As for column F (it would be broken down into individuals so 1 for 1. If I need 24 individual bottles like the image (I7), but only have 15 (F7) I would then like E7 to go to 5 as it originally only has 1 so 15+6=21 (therefore not enough). D7 would go from 1-0 then E7 would now become 5-2=3 because 2 of the 6 packs would now be used on F7 which originally had 15 now would have 27 but -25 for I7 for a final total of 2.

After the 24 bottles are entered the new variants for Milwaukee beer would be D7= 0 , E7=3, F7=3, that would be the cascade effect I'm looking for.

I know its a bit confusing with me trying to explain it but hopefully that simplifies it enough to figure out what I'm looking for.

PS. Thank you, for any time you are putting into this, very appreciated.

 

[dreid1011]

You are welcome.

It wasn't on their first image, but was on their second one (they made some changes between the two):

View attachment 103200

Yes, I did end up seeing that, thank you. Now just waiting on clarification on the "6 pack" / "12 pack" difference.

 

[dreid1011]

OK, so H7 all the way to H18 as well as I7 to I18 will all be entered manually. Once entered manually I would like it to auto-calculate D7-D18, E7-E18 as well as F7-F18. Here's the tricky part, I would like the D column to have a value of 24 (as per 24 beers per case) The E column to have a value of 6 (4-6 packs per case as far as column D is concerned) As for column F (it would be broken down into individuals so 1 for 1. If I need 24 individual bottles like the image (I7), but only have 15 (F7) I would then like E7 to go to 5 as it originally only has 1 so 15+6=21 (therefore not enough). D7 would go from 1-0 then E7 would now become 5-2=3 because 2 of the 6 packs would now be used on F7 which originally had 15 now would have 27 but -25 for I7 for a final total of 2.

After the 24 bottles are entered the new variants for Milwaukee beer would be D7= 0 , E7=3, F7=3, that would be the cascade effect I'm looking for.

I know its a bit confusing with me trying to explain it but hopefully that simplifies it enough to figure out what I'm looking for.

PS. Thank you, for any time you are putting into this, very appreciated.

That does help indeed. Now, would you be able to clarify why column Q is 12 packs and everything else is calculated as 6 packs, or do I not worry about the Final Inventory section?

Also, in column N for Beau's and Landshark, I figured out the formula for those, but I have different totals for those two compared to your second image.

 

[Holmes89]

That does help indeed. Now, would you be able to clarify why column Q is 12 packs and everything else is calculated as 6 packs, or do I not worry about the Final Inventory section?

Also, in column N for Beau's and Landshark, I figured out the formula for those, but I have different totals for those two compared to your second image.

please do not worry about that typo, just a mistake by me.

 

[dreid1011]

If I take more packs than are available, but there are enough singles to make up to difference, should it continue into singles or just go negative for packs?