Paul,

I may yet have to resort to 'helper' cells, as on reflection I think
that there will be sheets for Monday to Friday (except public
holidays), and the only way that the sheetname will be detectable is by
noting the last 5 (or so) sheet dates which can then be utilised in the
formula. Perhaps a button can do the Sheetcopy and date fix.

A second column would also be needed to go more than 5 days, due to the
limit of '7 nested' that Excel has, however, I think a score of 5 would
be sufficient for the OP's needs.

The formula may also need to be adjusted to show how many of the last 5
days, rather than the currently selected 'consecutive-working-backwards'
approach, but this will depend on requirements, and 'consecutive' looks
good for their needs, as it would be on a Parts Supply system.

Awaiting another OP response . . . .


paul Wrote:[color=blue]
wow!Thats what i call a formula!
I had in mind some helper columns,ie a 0 or 1 is diplayed depending on
the
no action/replenish result,and then i was going to sum over the
sheets.In
another set of cells would be the dates for today and previous two or
three
days and Indirect references to the sheets.I didnt get as far to figure
out
how to stop at the first no action (or 0) ...
good work Bryan
--
paul
remove nospam for email addy!



"Bryan Hessey" wrote:


Hi,

It's posibly that I expected a sheet for each day, but this may not
(yet) be the case. Your next sheet should be named 20050827 and
after
that 20050826 for you to get more than 1

If your sheets are not that, then you need specify a date and use
that
reference in the formula, thus if I enter a date (say Friday's date,
because I don't work Saturday nor Sunday) in cell J5 I would use the
formula:


=IF(D2<"replenish!,""",IF(INDIRECT(TEXT(J5-1,"yyyy")&TEXT(J5-1,"mm")&TEXT(J5-1,"dd")&"!d2")<"replenish!",1,IF(INDIRECT(TEXT( J5-2,"yyyy")&TEXT(J5-2,"mm")&TEXT(J5-2,"dd")&"!d2")<"replenish!",2,IF(INDIRECT(TEXT( J5-3,"yyyy")&TEXT(J5-3,"mm")&TEXT(J5-3,"dd")&"!d2")<"replenish!",3,IF(INDIRECT(TEXT( J5-4,"yyyy")&TEXT(J5-4,"mm")&TEXT(J5-4,"dd")&"!d2")<"replenish!",4,5)))))


If this doesn't work you can email me the book at
after you remove
remove.remove



blackgold21 Wrote:
Bryan,

Hey. I'm still getting 1's, I'm sorry to say. (Looks like you've
done
so
much work to help.)

I just wonder if my poor explanation is not the cause of the
trouble.
Is
there any way I can post the workbook?

"Bryan Hessey" wrote:


------------------------------------------------------------------------
Bryan Hessey's Profile:
http://www.excelforum.com/member.php...o&userid=21059
View this thread:
http://www.excelforum.com/showthread...hreadid=399574




--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=399574