SOLVED

# Excel formula help

Highlighted
Occasional Contributor

# Excel formula help

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.

9 Replies
Highlighted
Best Response confirmed by marc47555 (Occasional Contributor)
Solution

# Re: Excel formula help

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

# Re: Excel formula help

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

Highlighted

Highlighted

# Re: Excel formula help

@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

# Re: Excel formula help

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

Highlighted

# Re: Excel formula help

@Riny_van_Eekelen  Absolute LEGEND, thank you perfect

Highlighted

# Re: Excel formula help

@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

# Re: Excel formula help

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

Highlighted

# Re: Excel formula help

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