dynamic running total with absolute reference cell changes - Microsoft Community

hi all,

can please ask assistance on inventory table?

 

column a1:a10 have values populated when delivery arrives.

in cell b20 b51 has daily sales value.

in  column c20 c51 running daily total ( inventory balance minus daily sales).

but delivery unpredictable when arrives the "today balance" should be automatically increased added inventory value.

if there no inventory then : current inventory  - today sales

if there inventory then:  new inventory plus current inventory -  today sale

 the daily value needs to reflects in actual day (c column) that the inventory arrives' know how total balance value base on today price etc.

what formula should put in c20 c51 automatically achieve this?

much thanks,

 

hi,

i may have confused many people question. please allow me clarify.

column l4:l15 delivery column (that maximum delivery frequency in month).

column c22,c38,c54 ,  to c998 (every 16th rows) shift sales.

column l21, l37, l53 , l997 (every 16th rows) daily balance stock.

so, l21 = l4 (current , initial stock)  - c22 (shift sales) .

then on next shift sales, it's, l37 =   l21 (1st shift stock) -  c22 (2nd shift sales). 

that  case, until new delivery arrives, in l5, current shift stock l37 should added new delivery in l5 before shift sales c38 can deducted.

i hope have clarified self in that.

much thanks.

the formula in cell a26 =index($b$4:$b$15,match(a19,$b$4:$b$15,1),1)

gives delivery date use calculate delivery amount basis.

the formula in cell l21 should be:

=l4-c22+vlookup(a26,$b$4:$ae$15,11,false)

the formula in cell l37 should be:

=l21-c35

that  case, until new delivery arrives, in l5, current shift stock l52 should added new delivery in l5 before shift sales c54 can deducted.

(remember delivery happens in shift "a" always) so,

the formula in cell l53 should be:

=l37-c54+vlookup(a58,$b$4:$ae$15,11,false)

right? in perfect world there delivery every day yeah sure, know there isn't going delivery every day complications begin.

so in world do? how spreadsheet know there no delivery on day know how in world deal things?

this formula:

=index($b$4:$b$15,match(a19,$b$4:$b$15,1),1)

becomes useful again since if there no delivery date pass in cell a58 same date passed in cell a26.  therefore formula beginning in a58 there on end should include if function compare date passes there , compare date passed last time used in a26 should go this:

so formula in cell l53 , going forward should be:

=if(a58=a26, l37-c54, l37-c54+vlookup(a58,$b$4:$ae$15,11,false))

and formula second shift in cell l69 should same:

l53-c70

and on , forth...

are me now?

link final sample: 

https://1drv.ms/x/s!aiuzuhiqtf3fgftn0i_eusjvh-zn9w

please mark question answered if solution has answered question.

cheers

Office / Excel / Windows 10 / Office 2013