SOLVED

Excel formula help

Highlighted
Occasional Contributor

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.

 

1587024561149.png

9 Replies
Highlighted
Best Response confirmed by marc47555 (Occasional Contributor)
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.

Highlighted

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

Highlighted

@marc47555 Glad it worked!

Highlighted

@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

Highlighted

@marc47555 

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

Highlighted

@Riny_van_Eekelen  Absolute LEGEND, thank you perfect

Highlighted

@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

 

Highlighted

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

Highlighted

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