Apr 16 2020 03:11 AM
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.
Apr 16 2020 04:08 AM
SolutionReplicated 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.
Apr 16 2020 04:24 AM
@Riny_van_Eekelen That works perfectly thank you so much!! i have been trying to work this out for WEEKS!
Apr 16 2020 05:33 AM
@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
Apr 16 2020 06:23 AM
Please see the attached workbook. I believe it fixes the problem. Tweaked the formula a little bit.
Apr 17 2020 02:41 AM
@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
Apr 17 2020 04:34 AM
@marc47555 Have a look at the attached file. I believe it does what you require.
Apr 16 2020 04:08 AM
SolutionReplicated 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.