Running balance

Hi all,

See attached workbook which is a stock order workbook with a summary re-order sheet - (thanks to NBVC for the help on this)!!!

The problem I have is that as the running balance effects the re-order column the summary re-order sheet will re-order from a the date that stock is needed onwards until someone types in stock recieved to get the balance right.

I need another condition within the summary sheet sumproduct formula so that the order will only go through once and not be repeated until a new figure is added into the number issued column on a future date.

All help appriciated!!!

Can you elaborate a bit more on what you mean?.. give an example of what you are talking about.

All your daily sheets show a re-order amount of 10 in column T.

I am not sure what you are asking.

Shouldn't you be changing the values in those sheets instead?

Sorry for the confusion.

Essentially I want the daily sheets to remain the same including the re-order (column T) which has set conditions attached to it.

What I want to change is summary sheet sumproduct function so that it will continue to show the rows that need a re-order yet will only show them on the date they were put on (drop down box cell C4). If I select the next days date from cell C4 it also shows the same data. I don't want this on the next days summary sheet as the summary sheets are e-mailed daily to place orders.

And your right all i need to do is change the re-order value yet this relies on col S where if it has'nt gone back to the optimum level (stock level sheet colum r) it will show that a re-order is needed. The only way this can be changed is by col Q stock recieved have figures entered. This does'nt happen instantly (may take up to 2-3 days).

Sorry if this is still confusing....

How will you know when new data is in, then (i.e. when do expect a change in the represented data?).

Unless I am totally misinterpreting this, (someone may come up with a good formula method), but I think you're going to probably need some VBA to solve this.. something that would "clear" the sheet after shipment until you desire to show info again.

Unfortunately I am not the VBA guy you want.

If nobody assists in a day you may need to start a thread in the Programming forum and mention this thread.

Thanks NBVC, I think I am expecting too much this time!!!

I was thinking prehaps if adding a now statement to the

=IF(ROWS($A$1:$A1)>$D$4,"",INDEX(INDIRECT($C$4&"!A1:A130"),SMALL(IF(INDIRECT($C$4&"!AV1:AV130")=TRUE,ROW(INDIRECT($C$4&"!AV1:AV130"))),ROWS($A$1:$A1))))

function.

Maybe this would allow the summary sheet to only show orders that are needed on the day they run over the balance yet allowing the running balance to show on the next days daily sheets without the summary sheet continuing to show that there needs to be a re-order for the following days...

Do you mean to use today's date instead of the date in the drop down menu?

If so,

Replace formula in D4 with:

=COUNTIF(INDIRECT(TEXT(TODAY(),"dd.mm.yy")&"!AV:AV"),TRUE)

and add formula in D3:

=TEXT(TODAY(),"dd.mm.yy")

Then replace all reference in all formulas from $C$3 to $D$3 by going to Edit|Replace and entering $C$3 in the Find What field and entering $D$3 in the Replace With field.

Many thanks YET AGAIN!!!

You've been a great help.