Excel formula help

[marc47555]

Good Day,

I will try to explain to the best i can what i am trying to do!? i have uploaded the image with a better view to understanding what i am trying to achieve.

i basically have a list of numbers (stock Layers Items) going down in rows on a column.

There are 9 Layers to a Pallet - So i would basically like to paste the layers in the column then id like it to automatically assign the layers to the pallets, when 9 layers are reached the formula to pull through to the next pallet.

I hope this make sense to some one and your help is much appreciated.

[#2 · Accepted solution]

@marc47555 

Replicated your sheet and believe that this formula will do what you need.

=IF((SUM(F$5:F5)+$D6-SUM($E6:E6))<=9,$D6-SUM($E6:E6),9-SUM(F$5:F5)+SUM($E6:E6))

 Important that column E remains empty and the the headers in row 5 are text (i.e. not numbers) or that you insert an empty row 5. Enter the formula in F6 and copy it down and across as far as you need.

[#3]

@Riny_van_Eekelen That works perfectly thank you so much!! i have been trying to work this out for WEEKS!

[#4]

@marc47555 Glad it worked!

[#5]

@Riny_van_Eekelen it did thank you, how ever now they have told me i cant have more than 3 different layers on a pallet, ( layers are separate products ) I'm not sure if this is possible!? thanks anyway

[#6]

@marc47555 

Please see the attached workbook. I believe it fixes the problem. Tweaked the formula a little bit. 

[#7]

@Riny_van_Eekelen  Absolute LEGEND, thank you perfect

[#8]

@Riny_van_Eekelen  Thank you so much for you help the other day, those formulas work perfect for 3 of my sheets! :) 

how ever i have had to amend a sheet i have attached below with a note with what is needed im hoping you could help if not then thank you for trying .

Marc

[#9]

@marc47555 Will have look later and get back to you.

[#10]

@marc47555 Have a look at the attached file. I believe it does what you require.