Help needed with automatic formula population

oh its worked now

Try

in "Sheet1_1"

in H6

=IF($C6>H$5,"",IF($C6=H$5,MIN($G6,$E6),MIN($E6,MAX(INDEX($G$6:$G$400,MATCH(H$5,$C$6:$C$400,0))-SUM(OFFSET(H6,ROW($1:1),,6),0)))))

Copy across and down for FIRST week

NOT sure what calculation is starting 2nd week but hopefully you can adapt the above.

Thank you for your help with this John.

your formula works exactly right for week 1 but only for 70% of week 2 (that's probably down to my crap formula in week 1 though)

i have entered the original formulas into week 2 now and what the results should be and then done a copy using your formula and highlighted which cells are incorrect - i cant seem to adapt it to work as i'm not 100% sure on what it does

could you kindly point me in the right direction please.

sorry this it should of been this file

think I've sussed it.

I've changed the offset height to 365 and it is working now... for 2 weeks anyway, ill give the wholes year a go now.

in AC6

=IF($X6>AC$5,"",IF($X6=AC$5,MIN($AB6,$Z6),MIN($Z6,MAX(INDEX($AB$6:$AB$400,MATCH(AC$5,$X$6:$X$400,0))-SUM(OFFSET(AC6,1,,AC$5-$AC$5),0),0))))

Copy across and down


It had occurred to me to simply change the height to 365; The above changes the height dynamically but use 365 if it works.

it works great! thanks once again John

You are very welcome.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.