Formula Question

[blackgold21]

I have built a workbook in which I have inserted a formula to tell me whether
the contents of a supply bin needs replenishment or not. The formula I used
is: =IF(E3>F3,"REPLENISH!","No Action"). Each morning, I run a report to see
what parts have been used, which becomes a new sheet in the workbook.

Now, I want to add a formula that, whenever it sees "REPLENISH!," it will
back through the workbook to count whether that same part needed
replenishment on consecutive previous days. If it has, then the latest
worksheet will report the number of days that part that part has been in need
of replenishment.

Can you help me?

[Bernard Liengme]

Would be nice if you told us more about the workbook. Like were is the data
for each day of the week? - on the same worksheet or on its own worksheet.
Tell us what we need to help you.
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"blackgold21" <blackgold21@discussions.microsoft.com> wrote in message
news:BD934AFF-34D0-45DB-9F30-20172EFBAA64@microsoft.com...
>I have built a workbook in which I have inserted a formula to tell me
>whether
> the contents of a supply bin needs replenishment or not. The formula I
> used
> is: =IF(E3>F3,"REPLENISH!","No Action"). Each morning, I run a report to
> see
> what parts have been used, which becomes a new sheet in the workbook.
>
> Now, I want to add a formula that, whenever it sees "REPLENISH!," it will
> back through the workbook to count whether that same part needed
> replenishment on consecutive previous days. If it has, then the latest
> worksheet will report the number of days that part that part has been in
> need
> of replenishment.
>
> Can you help me?

[blackgold21]

Sorry if my description was unclear. Each day's report becomes a new sheet
in the same workbook

"Bernard Liengme" wrote:

> Would be nice if you told us more about the workbook. Like were is the data
> for each day of the week? - on the same worksheet or on its own worksheet.
> Tell us what we need to help you.
> --
> Bernard V Liengme
> www.stfx.ca/people/bliengme
> remove caps from email
>
> "blackgold21" <blackgold21@discussions.microsoft.com> wrote in message
> news:BD934AFF-34D0-45DB-9F30-20172EFBAA64@microsoft.com...
> >I have built a workbook in which I have inserted a formula to tell me
> >whether
> > the contents of a supply bin needs replenishment or not. The formula I
> > used
> > is: =IF(E3>F3,"REPLENISH!","No Action"). Each morning, I run a report to
> > see
> > what parts have been used, which becomes a new sheet in the workbook.
> >
> > Now, I want to add a formula that, whenever it sees "REPLENISH!," it will
> > back through the workbook to count whether that same part needed
> > replenishment on consecutive previous days. If it has, then the latest
> > worksheet will report the number of days that part that part has been in
> > need
> > of replenishment.
> >
> > Can you help me?
>
>
>

[blackgold21]

WOULD ANYONE ELSE CARE TO TAKE A STAB AT THIS QUESTION--PLEASE??

"blackgold21" wrote:

> Sorry if my description was unclear. Each day's report becomes a new sheet
> in the same workbook
>
> "Bernard Liengme" wrote:
>
> > Would be nice if you told us more about the workbook. Like were is the data
> > for each day of the week? - on the same worksheet or on its own worksheet.
> > Tell us what we need to help you.
> > --
> > Bernard V Liengme
> > www.stfx.ca/people/bliengme
> > remove caps from email
> >
> > "blackgold21" <blackgold21@discussions.microsoft.com> wrote in message
> > news:BD934AFF-34D0-45DB-9F30-20172EFBAA64@microsoft.com...
> > >I have built a workbook in which I have inserted a formula to tell me
> > >whether
> > > the contents of a supply bin needs replenishment or not. The formula I
> > > used
> > > is: =IF(E3>F3,"REPLENISH!","No Action"). Each morning, I run a report to
> > > see
> > > what parts have been used, which becomes a new sheet in the workbook.
> > >
> > > Now, I want to add a formula that, whenever it sees "REPLENISH!," it will
> > > back through the workbook to count whether that same part needed
> > > replenishment on consecutive previous days. If it has, then the latest
> > > worksheet will report the number of days that part that part has been in
> > > need
> > > of replenishment.
> > >
> > > Can you help me?
> >
> >
> >

[paul]

each days workbook has different data?If the same part number is replenished
they will be in a different cell on each day?.Each day has a completely
different sized data range?,How is each worksheet named?Give us a couple of
examples of data,say the position of the example part number that did need
replenishing say three days in a row,where is it on the sheet,how is the dta
arranged what does the surrounding data look like?
--
paul
remove nospam for email addy!



"blackgold21" wrote:

> WOULD ANYONE ELSE CARE TO TAKE A STAB AT THIS QUESTION--PLEASE??
>
> "blackgold21" wrote:
>
> > Sorry if my description was unclear. Each day's report becomes a new sheet
> > in the same workbook
> >
> > "Bernard Liengme" wrote:
> >
> > > Would be nice if you told us more about the workbook. Like were is the data
> > > for each day of the week? - on the same worksheet or on its own worksheet.
> > > Tell us what we need to help you.
> > > --
> > > Bernard V Liengme
> > > www.stfx.ca/people/bliengme
> > > remove caps from email
> > >
> > > "blackgold21" <blackgold21@discussions.microsoft.com> wrote in message
> > > news:BD934AFF-34D0-45DB-9F30-20172EFBAA64@microsoft.com...
> > > >I have built a workbook in which I have inserted a formula to tell me
> > > >whether
> > > > the contents of a supply bin needs replenishment or not. The formula I
> > > > used
> > > > is: =IF(E3>F3,"REPLENISH!","No Action"). Each morning, I run a report to
> > > > see
> > > > what parts have been used, which becomes a new sheet in the workbook.
> > > >
> > > > Now, I want to add a formula that, whenever it sees "REPLENISH!," it will
> > > > back through the workbook to count whether that same part needed
> > > > replenishment on consecutive previous days. If it has, then the latest
> > > > worksheet will report the number of days that part that part has been in
> > > > need
> > > > of replenishment.
> > > >
> > > > Can you help me?
> > >
> > >
> > >

[blackgold21]

Paul,

The list of parts is static (it is a standing inventory of parts for which I
have an agreement with vendors to replenish when needed); only the status is
dynamic. Therefore each day's worksheet has pretty much the same data range.
Worksheets are named for days of the year (i.e., 20050824, 200050825,
20050826, etc.)
As for examples of data, here is a try for a data range of the same 4
columns x 5 rows on each worksheet. Column header "MIN ORQ" represents
(Minimum Onhand Requirement). "R.L.T." represents (Replenishment Lag Time,
the formula I need help with):

(sheet 20050824)
MIN ORQ ON HAND ACTION R. L. T.
90 6 REPLENISH!
44 90 No Action
2000 907 REPLENISH!
1200 2122 No Action

(sheet 20050825)
MIN ORQ ON HAND ACTION R. L. T.
90 6 REPLENISH!
44 20 REPLENISH!
2000 907 REPLENISH!
1200 2000 No Action

(sheet 20050826)
MIN ORQ ON HAND ACTION R. L. T.
90 160 No Action
44 12 REPLENISH!
2000 2100 No Action
1200 24 REPLENISH!


"paul" wrote:

> each days workbook has different data?If the same part number is replenished
> they will be in a different cell on each day?.Each day has a completely
> different sized data range?,How is each worksheet named?Give us a couple of
> examples of data,say the position of the example part number that did need
> replenishing say three days in a row,where is it on the sheet,how is the dta
> arranged what does the surrounding data look like?
> --
> paul
> remove nospam for email addy!
>
>
>
> "blackgold21" wrote:
>
> > WOULD ANYONE ELSE CARE TO TAKE A STAB AT THIS QUESTION--PLEASE??
> >
> > "blackgold21" wrote:
> >
> > > Sorry if my description was unclear. Each day's report becomes a new sheet
> > > in the same workbook
> > >
> > > "Bernard Liengme" wrote:
> > >
> > > > Would be nice if you told us more about the workbook. Like were is the data
> > > > for each day of the week? - on the same worksheet or on its own worksheet.
> > > > Tell us what we need to help you.
> > > > --
> > > > Bernard V Liengme
> > > > www.stfx.ca/people/bliengme
> > > > remove caps from email
> > > >
> > > > "blackgold21" <blackgold21@discussions.microsoft.com> wrote in message
> > > > news:BD934AFF-34D0-45DB-9F30-20172EFBAA64@microsoft.com...
> > > > >I have built a workbook in which I have inserted a formula to tell me
> > > > >whether
> > > > > the contents of a supply bin needs replenishment or not. The formula I
> > > > > used
> > > > > is: =IF(E3>F3,"REPLENISH!","No Action"). Each morning, I run a report to
> > > > > see
> > > > > what parts have been used, which becomes a new sheet in the workbook.
> > > > >
> > > > > Now, I want to add a formula that, whenever it sees "REPLENISH!," it will
> > > > > back through the workbook to count whether that same part needed
> > > > > replenishment on consecutive previous days. If it has, then the latest
> > > > > worksheet will report the number of days that part that part has been in
> > > > > need
> > > > > of replenishment.
> > > > >
> > > > > Can you help me?
> > > >
> > > >
> > > >

[paul]

that is a very good response.The fact that your range is staic makes it
easier..............<thinks>
--
paul
remove nospam for email addy!



"blackgold21" wrote:

> Paul,
>
> The list of parts is static (it is a standing inventory of parts for which I
> have an agreement with vendors to replenish when needed); only the status is
> dynamic. Therefore each day's worksheet has pretty much the same data range.
> Worksheets are named for days of the year (i.e., 20050824, 200050825,
> 20050826, etc.)
> As for examples of data, here is a try for a data range of the same 4
> columns x 5 rows on each worksheet. Column header "MIN ORQ" represents
> (Minimum Onhand Requirement). "R.L.T." represents (Replenishment Lag Time,
> the formula I need help with):
>
> (sheet 20050824)
> MIN ORQ ON HAND ACTION R. L. T.
> 90 6 REPLENISH!
> 44 90 No Action
> 2000 907 REPLENISH!
> 1200 2122 No Action
>
> (sheet 20050825)
> MIN ORQ ON HAND ACTION R. L. T.
> 90 6 REPLENISH!
> 44 20 REPLENISH!
> 2000 907 REPLENISH!
> 1200 2000 No Action
>
> (sheet 20050826)
> MIN ORQ ON HAND ACTION R. L. T.
> 90 160 No Action
> 44 12 REPLENISH!
> 2000 2100 No Action
> 1200 24 REPLENISH!
>
>
> "paul" wrote:
>
> > each days workbook has different data?If the same part number is replenished
> > they will be in a different cell on each day?.Each day has a completely
> > different sized data range?,How is each worksheet named?Give us a couple of
> > examples of data,say the position of the example part number that did need
> > replenishing say three days in a row,where is it on the sheet,how is the dta
> > arranged what does the surrounding data look like?
> > --
> > paul
> > remove nospam for email addy!
> >
> >
> >
> > "blackgold21" wrote:
> >
> > > WOULD ANYONE ELSE CARE TO TAKE A STAB AT THIS QUESTION--PLEASE??
> > >
> > > "blackgold21" wrote:
> > >
> > > > Sorry if my description was unclear. Each day's report becomes a new sheet
> > > > in the same workbook
> > > >
> > > > "Bernard Liengme" wrote:
> > > >
> > > > > Would be nice if you told us more about the workbook. Like were is the data
> > > > > for each day of the week? - on the same worksheet or on its own worksheet.
> > > > > Tell us what we need to help you.
> > > > > --
> > > > > Bernard V Liengme
> > > > > www.stfx.ca/people/bliengme
> > > > > remove caps from email
> > > > >
> > > > > "blackgold21" <blackgold21@discussions.microsoft.com> wrote in message
> > > > > news:BD934AFF-34D0-45DB-9F30-20172EFBAA64@microsoft.com...
> > > > > >I have built a workbook in which I have inserted a formula to tell me
> > > > > >whether
> > > > > > the contents of a supply bin needs replenishment or not. The formula I
> > > > > > used
> > > > > > is: =IF(E3>F3,"REPLENISH!","No Action"). Each morning, I run a report to
> > > > > > see
> > > > > > what parts have been used, which becomes a new sheet in the workbook.
> > > > > >
> > > > > > Now, I want to add a formula that, whenever it sees "REPLENISH!," it will
> > > > > > back through the workbook to count whether that same part needed
> > > > > > replenishment on consecutive previous days. If it has, then the latest
> > > > > > worksheet will report the number of days that part that part has been in
> > > > > > need
> > > > > > of replenishment.
> > > > > >
> > > > > > Can you help me?
> > > > >
> > > > >
> > > > >

[blackgold21]

Thanks for your speedy reply to let me know you got the example and are
thinking things over!

"paul" wrote:

> that is a very good response.The fact that your range is staic makes it
> easier..............<thinks>
> --
> paul
> remove nospam for email addy!
>
>
>
> "blackgold21" wrote:
>
> > Paul,
> >
> > The list of parts is static (it is a standing inventory of parts for which I
> > have an agreement with vendors to replenish when needed); only the status is
> > dynamic. Therefore each day's worksheet has pretty much the same data range.
> > Worksheets are named for days of the year (i.e., 20050824, 200050825,
> > 20050826, etc.)
> > As for examples of data, here is a try for a data range of the same 4
> > columns x 5 rows on each worksheet. Column header "MIN ORQ" represents
> > (Minimum Onhand Requirement). "R.L.T." represents (Replenishment Lag Time,
> > the formula I need help with):
> >
> > (sheet 20050824)
> > MIN ORQ ON HAND ACTION R. L. T.
> > 90 6 REPLENISH!
> > 44 90 No Action
> > 2000 907 REPLENISH!
> > 1200 2122 No Action
> >
> > (sheet 20050825)
> > MIN ORQ ON HAND ACTION R. L. T.
> > 90 6 REPLENISH!
> > 44 20 REPLENISH!
> > 2000 907 REPLENISH!
> > 1200 2000 No Action
> >
> > (sheet 20050826)
> > MIN ORQ ON HAND ACTION R. L. T.
> > 90 160 No Action
> > 44 12 REPLENISH!
> > 2000 2100 No Action
> > 1200 24 REPLENISH!
> >
> >
> > "paul" wrote:
> >
> > > each days workbook has different data?If the same part number is replenished
> > > they will be in a different cell on each day?.Each day has a completely
> > > different sized data range?,How is each worksheet named?Give us a couple of
> > > examples of data,say the position of the example part number that did need
> > > replenishing say three days in a row,where is it on the sheet,how is the dta
> > > arranged what does the surrounding data look like?
> > > --
> > > paul
> > > remove nospam for email addy!
> > >
> > >
> > >
> > > "blackgold21" wrote:
> > >
> > > > WOULD ANYONE ELSE CARE TO TAKE A STAB AT THIS QUESTION--PLEASE??
> > > >
> > > > "blackgold21" wrote:
> > > >
> > > > > Sorry if my description was unclear. Each day's report becomes a new sheet
> > > > > in the same workbook
> > > > >
> > > > > "Bernard Liengme" wrote:
> > > > >
> > > > > > Would be nice if you told us more about the workbook. Like were is the data
> > > > > > for each day of the week? - on the same worksheet or on its own worksheet.
> > > > > > Tell us what we need to help you.
> > > > > > --
> > > > > > Bernard V Liengme
> > > > > > www.stfx.ca/people/bliengme
> > > > > > remove caps from email
> > > > > >
> > > > > > "blackgold21" <blackgold21@discussions.microsoft.com> wrote in message
> > > > > > news:BD934AFF-34D0-45DB-9F30-20172EFBAA64@microsoft.com...
> > > > > > >I have built a workbook in which I have inserted a formula to tell me
> > > > > > >whether
> > > > > > > the contents of a supply bin needs replenishment or not. The formula I
> > > > > > > used
> > > > > > > is: =IF(E3>F3,"REPLENISH!","No Action"). Each morning, I run a report to
> > > > > > > see
> > > > > > > what parts have been used, which becomes a new sheet in the workbook.
> > > > > > >
> > > > > > > Now, I want to add a formula that, whenever it sees "REPLENISH!," it will
> > > > > > > back through the workbook to count whether that same part needed
> > > > > > > replenishment on consecutive previous days. If it has, then the latest
> > > > > > > worksheet will report the number of days that part that part has been in
> > > > > > > need
> > > > > > > of replenishment.
> > > > > > >
> > > > > > > Can you help me?
> > > > > >
> > > > > >
> > > > > >

[Bryan Hessey]

Assuming the 'replenish' word is in colmn G, then for row 10 the formula

=IF(G10<>"replenish,""",IF(INDIRECT(TEXT(NOW()-1,"yyyy")&TEXT(NOW()-1,"mm")&TEXT(NOW()-1,"dd")&"!g10")<>"replenish",1,IF(INDIRECT(TEXT(NOW()-2,"yyyy")&TEXT(NOW()-2,"mm")&TEXT(NOW()-2,"dd")&"!g10")<>"replenish",2,IF(INDIRECT(TEXT(NOW()-3,"yyyy")&TEXT(NOW()-3,"mm")&TEXT(NOW()-3,"dd")&"!g10")<>"replenish",2,IF(INDIRECT(TEXT(NOW()-4,"yyyy")&TEXT(NOW()-4,"mm")&TEXT(NOW()-4,"dd")&"!g10")<>"replenish",3,4)))))

works for today and the prior 4 days, but stops counting on the first non-'replenish' day.

Thanks for your speedy reply to let me know you got the example and are
thinking things over!

"paul" wrote:

> that is a very good response.The fact that your range is staic makes it
> easier..............<thinks>
> --
> paul
> remove nospam for email addy!
>
>
>
> "blackgold21" wrote:
>
> > Paul,
> >
> > The list of parts is static (it is a standing inventory of parts for which I
> > have an agreement with vendors to replenish when needed); only the status is
> > dynamic. Therefore each day's worksheet has pretty much the same data range.
> > Worksheets are named for days of the year (i.e., 20050824, 200050825,
> > 20050826, etc.)
> > As for examples of data, here is a try for a data range of the same 4
> > columns x 5 rows on each worksheet. Column header "MIN ORQ" represents
> > (Minimum Onhand Requirement). "R.L.T." represents (Replenishment Lag Time,
> > the formula I need help with):
> >
> > (sheet 20050824)
> > MIN ORQ ON HAND ACTION R. L. T.
> > 90 6 REPLENISH!
> > 44 90 No Action
> > 2000 907 REPLENISH!
> > 1200 2122 No Action
> >
> > (sheet 20050825)
> > MIN ORQ ON HAND ACTION R. L. T.
> > 90 6 REPLENISH!
> > 44 20 REPLENISH!
> > 2000 907 REPLENISH!
> > 1200 2000 No Action
> >
> > (sheet 20050826)
> > MIN ORQ ON HAND ACTION R. L. T.
> > 90 160 No Action
> > 44 12 REPLENISH!
> > 2000 2100 No Action
> > 1200 24 REPLENISH!
> >
> >
> > "paul" wrote:
> >
> > > each days workbook has different data?If the same part number is replenished
> > > they will be in a different cell on each day?.Each day has a completely
> > > different sized data range?,How is each worksheet named?Give us a couple of
> > > examples of data,say the position of the example part number that did need
> > > replenishing say three days in a row,where is it on the sheet,how is the dta
> > > arranged what does the surrounding data look like?
> > > --
> > > paul
> > > remove nospam for email addy!
> > >
> > >
> > >
> > > "blackgold21" wrote:
> > >
> > > > WOULD ANYONE ELSE CARE TO TAKE A STAB AT THIS QUESTION--PLEASE??
> > > >
> > > > "blackgold21" wrote:
> > > >
> > > > > Sorry if my description was unclear. Each day's report becomes a new sheet
> > > > > in the same workbook
> > > > >
> > > > > "Bernard Liengme" wrote:
> > > > >
> > > > > > Would be nice if you told us more about the workbook. Like were is the data
> > > > > > for each day of the week? - on the same worksheet or on its own worksheet.
> > > > > > Tell us what we need to help you.
> > > > > > --
> > > > > > Bernard V Liengme
> > > > > > www.stfx.ca/people/bliengme
> > > > > > remove caps from email
> > > > > >
> > > > > > "blackgold21" <blackgold21@discussions.microsoft.com> wrote in message
> > > > > > news:BD934AFF-34D0-45DB-9F30-20172EFBAA64@microsoft.com...
> > > > > > >I have built a workbook in which I have inserted a formula to tell me
> > > > > > >whether
> > > > > > > the contents of a supply bin needs replenishment or not. The formula I
> > > > > > > used
> > > > > > > is: =IF(E3>F3,"REPLENISH!","No Action"). Each morning, I run a report to
> > > > > > > see
> > > > > > > what parts have been used, which becomes a new sheet in the workbook.
> > > > > > >
> > > > > > > Now, I want to add a formula that, whenever it sees "REPLENISH!," it will
> > > > > > > back through the workbook to count whether that same part needed
> > > > > > > replenishment on consecutive previous days. If it has, then the latest
> > > > > > > worksheet will report the number of days that part that part has been in
> > > > > > > need
> > > > > > > of replenishment.
> > > > > > >
> > > > > > > Can you help me?
> > > > > >
> > > > > >
> > > > > >

[blackgold21]

I applied Bryan's formula to my workbook in the appropriate column (cells
E2-E5 in all three worksheets), where the range B2-E5 looks like this:

MIN ORQ ON HAND ACTION R.L.T.
90 160 No Action 1
44 12 REPLENISH! 1
2000 2100 No Action 1
1200 24 REPLENISH! 1

I customized Bryan's formula to match my spreadsheet architecture, somewhat.
Instead of the destination G10, (see his formula below) I applied the
appropriate cell address E2-E5 on all worksheets. The only product the
formula returned for me is 1. I see now that when I substituted new cell
addresses for the address in his formula, I inadvertantly substituted
upper-case letters for lower-case ones. Is that the root cause of the
problem???

ANY IDEAS?


"Bryan Hessey" wrote:

>
> Assuming the 'replenish' word is in colmn G, then for row 10 the formula
>
>
>
> =IF(G10<>"replenish,""",IF(INDIRECT(TEXT(NOW()-1,"yyyy")&TEXT(NOW()-1,"mm")&TEXT(NOW()-1,"dd")&"!g10")<>"replenish",1,IF(INDIRECT(TEXT(NOW()-2,"yyyy")&TEXT(NOW()-2,"mm")&TEXT(NOW()-2,"dd")&"!g10")<>"replenish",2,IF(INDIRECT(TEXT(NOW()-3,"yyyy")&TEXT(NOW()-3,"mm")&TEXT(NOW()-3,"dd")&"!g10")<>"replenish",2,IF(INDIRECT(TEXT(NOW()-4,"yyyy")&TEXT(NOW()-4,"mm")&TEXT(NOW()-4,"dd")&"!g10")<>"replenish",3,4)))))
>
> works for today and the prior 4 days, but stops counting on the first
> non-'replenish' day.
>
>
>
>
>
> blackgold21 Wrote:
> > Thanks for your speedy reply to let me know you got the example and are
> > thinking things over!
> >
> > "paul" wrote:
> >
> > > that is a very good response.The fact that your range is staic makes
> > it
> > > easier..............<thinks>
> > > --
> > > paul
> > > remove nospam for email addy!
> > >
> > >
> > >
> > > "blackgold21" wrote:
> > >
> > > > Paul,
> > > >
> > > > The list of parts is static (it is a standing inventory of parts
> > for which I
> > > > have an agreement with vendors to replenish when needed); only the
> > status is
> > > > dynamic. Therefore each day's worksheet has pretty much the same
> > data range.
> > > > Worksheets are named for days of the year (i.e., 20050824,
> > 200050825,
> > > > 20050826, etc.)
> > > > As for examples of data, here is a try for a data range of the same
> > 4
> > > > columns x 5 rows on each worksheet. Column header "MIN ORQ"
> > represents
> > > > (Minimum Onhand Requirement). "R.L.T." represents (Replenishment
> > Lag Time,
> > > > the formula I need help with):
> > > >
> > > > (sheet 20050824)
> > > > MIN ORQ ON HAND ACTION R. L. T.
> > > > 90 6 REPLENISH!
> > > > 44 90 No Action
> > > > 2000 907 REPLENISH!
> > > > 1200 2122 No Action
> > > >
> > > > (sheet 20050825)
> > > > MIN ORQ ON HAND ACTION R. L. T.
> > > > 90 6 REPLENISH!
> > > > 44 20 REPLENISH!
> > > > 2000 907 REPLENISH!
> > > > 1200 2000 No Action
> > > >
> > > > (sheet 20050826)
> > > > MIN ORQ ON HAND ACTION R. L. T.
> > > > 90 160 No Action
> > > > 44 12 REPLENISH!
> > > > 2000 2100 No Action
> > > > 1200 24 REPLENISH!
> > > >
> > > >
> > > > "paul" wrote:
> > > >
> > > > > each days workbook has different data?If the same part number is
> > replenished
> > > > > they will be in a different cell on each day?.Each day has a
> > completely
> > > > > different sized data range?,How is each worksheet named?Give us a
> > couple of
> > > > > examples of data,say the position of the example part number that
> > did need
> > > > > replenishing say three days in a row,where is it on the sheet,how
> > is the dta
> > > > > arranged what does the surrounding data look like?
> > > > > --
> > > > > paul
> > > > > remove nospam for email addy!
> > > > >
> > > > >
> > > > >
> > > > > "blackgold21" wrote:
> > > > >
> > > > > > WOULD ANYONE ELSE CARE TO TAKE A STAB AT THIS
> > QUESTION--PLEASE??
> > > > > >
> > > > > > "blackgold21" wrote:
> > > > > >
> > > > > > > Sorry if my description was unclear. Each day's report
> > becomes a new sheet
> > > > > > > in the same workbook
> > > > > > >
> > > > > > > "Bernard Liengme" wrote:
> > > > > > >
> > > > > > > > Would be nice if you told us more about the workbook. Like
> > were is the data
> > > > > > > > for each day of the week? - on the same worksheet or on its
> > own worksheet.
> > > > > > > > Tell us what we need to help you.
> > > > > > > > --
> > > > > > > > Bernard V Liengme
> > > > > > > > www.stfx.ca/people/bliengme
> > > > > > > > remove caps from email
> > > > > > > >
> > > > > > > > "blackgold21" <blackgold21@discussions.microsoft.com> wrote
> > in message
> > > > > > > > news:BD934AFF-34D0-45DB-9F30-20172EFBAA64@microsoft.com...
> > > > > > > > >I have built a workbook in which I have inserted a formula
> > to tell me
> > > > > > > > >whether
> > > > > > > > > the contents of a supply bin needs replenishment or not.
> > The formula I
> > > > > > > > > used
> > > > > > > > > is: =IF(E3>F3,"REPLENISH!","No Action"). Each morning, I
> > run a report to
> > > > > > > > > see
> > > > > > > > > what parts have been used, which becomes a new sheet in
> > the workbook.
> > > > > > > > >
> > > > > > > > > Now, I want to add a formula that, whenever it sees
> > "REPLENISH!," it will
> > > > > > > > > back through the workbook to count whether that same part
> > needed
> > > > > > > > > replenishment on consecutive previous days. If it has,
> > then the latest
> > > > > > > > > worksheet will report the number of days that part that
> > part has been in
> > > > > > > > > need
> > > > > > > > > of replenishment.
> > > > > > > > >
> > > > > > > > > Can you help me?
> > > > > > > >
> > > > > > > >
> > > > > > > >
>
>
> --
> 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
>
>

[Bryan Hessey]

Hi,

It should'nt be case sensitive, and the 'G10' amended to 'D2' certainly isn't case affected.

I did miss the ! from REPLENISH! and also got the count wrong, where 2, 3 & 4 should have been 3, 4 & 5

amended to column D from row 2 is:

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


hope this helps

I applied Bryan's formula to my workbook in the appropriate column (cells
E2-E5 in all three worksheets), where the range B2-E5 looks like this:

MIN ORQ ON HAND ACTION R.L.T.
90 160 No Action 1
44 12 REPLENISH! 1
2000 2100 No Action 1
1200 24 REPLENISH! 1

I customized Bryan's formula to match my spreadsheet architecture, somewhat.
Instead of the destination G10, (see his formula below) I applied the
appropriate cell address E2-E5 on all worksheets. The only product the
formula returned for me is 1. I see now that when I substituted new cell
addresses for the address in his formula, I inadvertantly substituted
upper-case letters for lower-case ones. Is that the root cause of the
problem???

ANY IDEAS?


"Bryan Hessey" wrote:

>
> Assuming the 'replenish' word is in colmn G, then for row 10 the formula
>
>
>
> =IF(G10<>"replenish,""",IF(INDIRECT(TEXT(NOW()-1,"yyyy")&TEXT(NOW()-1,"mm")&TEXT(NOW()-1,"dd")&"!g10")<>"replenish",1,IF(INDIRECT(TEXT(NOW()-2,"yyyy")&TEXT(NOW()-2,"mm")&TEXT(NOW()-2,"dd")&"!g10")<>"replenish",2,IF(INDIRECT(TEXT(NOW()-3,"yyyy")&TEXT(NOW()-3,"mm")&TEXT(NOW()-3,"dd")&"!g10")<>"replenish",2,IF(INDIRECT(TEXT(NOW()-4,"yyyy")&TEXT(NOW()-4,"mm")&TEXT(NOW()-4,"dd")&"!g10")<>"replenish",3,4)))))
>
> works for today and the prior 4 days, but stops counting on the first
> non-'replenish' day.
>
>
>
>
>
> blackgold21 Wrote:
> > Thanks for your speedy reply to let me know you got the example and are
> > thinking things over!
> >
> > "paul" wrote:
> >
> > > that is a very good response.The fact that your range is staic makes
> > it
> > > easier..............<thinks>
> > > --
> > > paul
> > > remove nospam for email addy!
> > >
> > >
> > >
> > > "blackgold21" wrote:
> > >
> > > > Paul,
> > > >
> > > > The list of parts is static (it is a standing inventory of parts
> > for which I
> > > > have an agreement with vendors to replenish when needed); only the
> > status is
> > > > dynamic. Therefore each day's worksheet has pretty much the same
> > data range.
> > > > Worksheets are named for days of the year (i.e., 20050824,
> > 200050825,
> > > > 20050826, etc.)
> > > > As for examples of data, here is a try for a data range of the same
> > 4
> > > > columns x 5 rows on each worksheet. Column header "MIN ORQ"
> > represents
> > > > (Minimum Onhand Requirement). "R.L.T." represents (Replenishment
> > Lag Time,
> > > > the formula I need help with):
> > > >
> > > > (sheet 20050824)
> > > > MIN ORQ ON HAND ACTION R. L. T.
> > > > 90 6 REPLENISH!
> > > > 44 90 No Action
> > > > 2000 907 REPLENISH!
> > > > 1200 2122 No Action
> > > >
> > > > (sheet 20050825)
> > > > MIN ORQ ON HAND ACTION R. L. T.
> > > > 90 6 REPLENISH!
> > > > 44 20 REPLENISH!
> > > > 2000 907 REPLENISH!
> > > > 1200 2000 No Action
> > > >
> > > > (sheet 20050826)
> > > > MIN ORQ ON HAND ACTION R. L. T.
> > > > 90 160 No Action
> > > > 44 12 REPLENISH!
> > > > 2000 2100 No Action
> > > > 1200 24 REPLENISH!
> > > >
> > > >
> > > > "paul" wrote:
> > > >
> > > > > each days workbook has different data?If the same part number is
> > replenished
> > > > > they will be in a different cell on each day?.Each day has a
> > completely
> > > > > different sized data range?,How is each worksheet named?Give us a
> > couple of
> > > > > examples of data,say the position of the example part number that
> > did need
> > > > > replenishing say three days in a row,where is it on the sheet,how
> > is the dta
> > > > > arranged what does the surrounding data look like?
> > > > > --
> > > > > paul
> > > > > remove nospam for email addy!
> > > > >
> > > > >
> > > > >
> > > > > "blackgold21" wrote:
> > > > >
> > > > > > WOULD ANYONE ELSE CARE TO TAKE A STAB AT THIS
> > QUESTION--PLEASE??
> > > > > >
> > > > > > "blackgold21" wrote:
> > > > > >
> > > > > > > Sorry if my description was unclear. Each day's report
> > becomes a new sheet
> > > > > > > in the same workbook
> > > > > > >
> > > > > > > "Bernard Liengme" wrote:
> > > > > > >
> > > > > > > > Would be nice if you told us more about the workbook. Like
> > were is the data
> > > > > > > > for each day of the week? - on the same worksheet or on its
> > own worksheet.
> > > > > > > > Tell us what we need to help you.
> > > > > > > > --
> > > > > > > > Bernard V Liengme
> > > > > > > > www.stfx.ca/people/bliengme
> > > > > > > > remove caps from email
> > > > > > > >
> > > > > > > > "blackgold21" <blackgold21@discussions.microsoft.com> wrote
> > in message
> > > > > > > > news:BD934AFF-34D0-45DB-9F30-20172EFBAA64@microsoft.com...
> > > > > > > > >I have built a workbook in which I have inserted a formula
> > to tell me
> > > > > > > > >whether
> > > > > > > > > the contents of a supply bin needs replenishment or not.
> > The formula I
> > > > > > > > > used
> > > > > > > > > is: =IF(E3>F3,"REPLENISH!","No Action"). Each morning, I
> > run a report to
> > > > > > > > > see
> > > > > > > > > what parts have been used, which becomes a new sheet in
> > the workbook.
> > > > > > > > >
> > > > > > > > > Now, I want to add a formula that, whenever it sees
> > "REPLENISH!," it will
> > > > > > > > > back through the workbook to count whether that same part
> > needed
> > > > > > > > > replenishment on consecutive previous days. If it has,
> > then the latest
> > > > > > > > > worksheet will report the number of days that part that
> > part has been in
> > > > > > > > > need
> > > > > > > > > of replenishment.
> > > > > > > > >
> > > > > > > > > Can you help me?
> > > > > > > >
> > > > > > > >
> > > > > > > >
>
>
> --
> 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
>
>

[blackgold21]

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:

>
> Hi,
>
> It should'nt be case sensitive, and the 'G10' amended to 'D2' certainly
> isn't case affected.
>
> I did miss the ! from REPLENISH! and also got the count wrong, where 2,
> 3 & 4 should have been 3, 4 & 5
>
> amended to column D from row 2 is:
>
>
> =IF(D2<>"replenish!,""",IF(INDIRECT(TEXT(NOW()-1,"yyyy")&TEXT(NOW()-1,"mm")&TEXT(NOW()-1,"dd")&"!d2")<>"replenish!",1,IF(INDIRECT(TEXT(NOW()-2,"yyyy")&TEXT(NOW()-2,"mm")&TEXT(NOW()-2,"dd")&"!d2")<>"replenish!",2,IF(INDIRECT(TEXT(NOW()-3,"yyyy")&TEXT(NOW()-3,"mm")&TEXT(NOW()-3,"dd")&"!d2")<>"replenish!",3,IF(INDIRECT(TEXT(NOW()-4,"yyyy")&TEXT(NOW()-4,"mm")&TEXT(NOW()-4,"dd")&"!d2")<>"replenish!",4,5)))))
>
>
> hope this helps
>
> blackgold21 Wrote:
> > I applied Bryan's formula to my workbook in the appropriate column
> > (cells
> > E2-E5 in all three worksheets), where the range B2-E5 looks like this:
> >
> > MIN ORQ ON HAND ACTION R.L.T.
> > 90 160 No Action 1
> > 44 12 REPLENISH! 1
> > 2000 2100 No Action 1
> > 1200 24 REPLENISH! 1
> >
> > I customized Bryan's formula to match my spreadsheet architecture,
> > somewhat.
> > Instead of the destination G10, (see his formula below) I applied the
> > appropriate cell address E2-E5 on all worksheets. The only product
> > the
> > formula returned for me is 1. I see now that when I substituted new
> > cell
> > addresses for the address in his formula, I inadvertantly substituted
> > upper-case letters for lower-case ones. Is that the root cause of the
> > problem???
> >
> > ANY IDEAS?
> >
> >
> > "Bryan Hessey" wrote:
> >
> > >
> > > Assuming the 'replenish' word is in colmn G, then for row 10 the
> > formula
> > >
> > >
> > >
> > >
> > =IF(G10<>"replenish,""",IF(INDIRECT(TEXT(NOW()-1,"yyyy")&TEXT(NOW()-1,"mm")&TEXT(NOW()-1,"dd")&"!g10")<>"replenish",1,IF(INDIRECT(TEXT(NOW()-2,"yyyy")&TEXT(NOW()-2,"mm")&TEXT(NOW()-2,"dd")&"!g10")<>"replenish",2,IF(INDIRECT(TEXT(NOW()-3,"yyyy")&TEXT(NOW()-3,"mm")&TEXT(NOW()-3,"dd")&"!g10")<>"replenish",2,IF(INDIRECT(TEXT(NOW()-4,"yyyy")&TEXT(NOW()-4,"mm")&TEXT(NOW()-4,"dd")&"!g10")<>"replenish",3,4)))))
> > >
> > > works for today and the prior 4 days, but stops counting on the
> > first
> > > non-'replenish' day.
> > >
> > >
> > >
> > >
> > >
> > > blackgold21 Wrote:
> > > > Thanks for your speedy reply to let me know you got the example and
> > are
> > > > thinking things over!
> > > >
> > > > "paul" wrote:
> > > >
> > > > > that is a very good response.The fact that your range is staic
> > makes
> > > > it
> > > > > easier..............<thinks>
> > > > > --
> > > > > paul
> > > > > remove nospam for email addy!
> > > > >
> > > > >
> > > > >
> > > > > "blackgold21" wrote:
> > > > >
> > > > > > Paul,
> > > > > >
> > > > > > The list of parts is static (it is a standing inventory of
> > parts
> > > > for which I
> > > > > > have an agreement with vendors to replenish when needed); only
> > the
> > > > status is
> > > > > > dynamic. Therefore each day's worksheet has pretty much the
> > same
> > > > data range.
> > > > > > Worksheets are named for days of the year (i.e., 20050824,
> > > > 200050825,
> > > > > > 20050826, etc.)
> > > > > > As for examples of data, here is a try for a data range of the
> > same
> > > > 4
> > > > > > columns x 5 rows on each worksheet. Column header "MIN ORQ"
> > > > represents
> > > > > > (Minimum Onhand Requirement). "R.L.T." represents
> > (Replenishment
> > > > Lag Time,
> > > > > > the formula I need help with):
> > > > > >
> > > > > > (sheet 20050824)
> > > > > > MIN ORQ ON HAND ACTION R. L. T.
> > > > > > 90 6 REPLENISH!
> > > > > > 44 90 No Action
> > > > > > 2000 907 REPLENISH!
> > > > > > 1200 2122 No Action
> > > > > >
> > > > > > (sheet 20050825)
> > > > > > MIN ORQ ON HAND ACTION R. L. T.
> > > > > > 90 6 REPLENISH!
> > > > > > 44 20 REPLENISH!
> > > > > > 2000 907 REPLENISH!
> > > > > > 1200 2000 No Action
> > > > > >
> > > > > > (sheet 20050826)
> > > > > > MIN ORQ ON HAND ACTION R. L. T.
> > > > > > 90 160 No Action
> > > > > > 44 12 REPLENISH!
> > > > > > 2000 2100 No Action
> > > > > > 1200 24 REPLENISH!
> > > > > >
> > > > > >
> > > > > > "paul" wrote:
> > > > > >
> > > > > > > each days workbook has different data?If the same part number
> > is
> > > > replenished
> > > > > > > they will be in a different cell on each day?.Each day has a
> > > > completely
> > > > > > > different sized data range?,How is each worksheet named?Give
> > us a
> > > > couple of
> > > > > > > examples of data,say the position of the example part number
> > that
> > > > did need
> > > > > > > replenishing say three days in a row,where is it on the
> > sheet,how
> > > > is the dta
> > > > > > > arranged what does the surrounding data look like?
> > > > > > > --
> > > > > > > paul
> > > > > > > remove nospam for email addy!
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > "blackgold21" wrote:
> > > > > > >
> > > > > > > > WOULD ANYONE ELSE CARE TO TAKE A STAB AT THIS
> > > > QUESTION--PLEASE??
> > > > > > > >
> > > > > > > > "blackgold21" wrote:
> > > > > > > >
> > > > > > > > > Sorry if my description was unclear. Each day's report
> > > > becomes a new sheet
> > > > > > > > > in the same workbook
> > > > > > > > >
> > > > > > > > > "Bernard Liengme" wrote:
> > > > > > > > >
> > > > > > > > > > Would be nice if you told us more about the workbook.
> > Like
> > > > were is the data
> > > > > > > > > > for each day of the week? - on the same worksheet or on
> > its
> > > > own worksheet.
> > > > > > > > > > Tell us what we need to help you.
> > > > > > > > > > --
> > > > > > > > > > Bernard V Liengme
> > > > > > > > > > www.stfx.ca/people/bliengme
> > > > > > > > > > remove caps from email
> > > > > > > > > >
> > > > > > > > > > "blackgold21" <blackgold21@discussions.microsoft.com>
> > wrote
> > > > in message
> > > > > > > > > >
> > news:BD934AFF-34D0-45DB-9F30-20172EFBAA64@microsoft.com...
> > > > > > > > > > >I have built a workbook in which I have inserted a
> > formula
> > > > to tell me
> > > > > > > > > > >whether
> > > > > > > > > > > the contents of a supply bin needs replenishment or
> > not.
> > > > The formula I
> > > > > > > > > > > used
> > > > > > > > > > > is: =IF(E3>F3,"REPLENISH!","No Action"). Each
> > morning, I
> > > > run a report to
> > > > > > > > > > > see
> > > > > > > > > > > what parts have been used, which becomes a new sheet
> > in
> > > > the workbook.
> > > > > > > > > > >
> > > > > > > > > > > Now, I want to add a formula that, whenever it sees
> > > > "REPLENISH!," it will
> > > > > > > > > > > back through the workbook to count whether that same
> > part
> > > > needed
> > > > > > > > > > > replenishment on consecutive previous days. If it
> > has,
> > > > then the latest
> > > > > > > > > > > worksheet will report the number of days that part
> > that
> > > > part has been in
> > > > > > > > > > > need
> > > > > > > > > > > of replenishment.
> > > > > > > > > > >
> > > > > > > > > > > Can you help me?
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > >
> > >
> > > --
> > > 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
> > >
> > >
>
>
> --
> 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
>
>

[Bryan Hessey]

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 Bryan.Hessey@remove.removeMinterEllison.com after you remove remove.remove

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:

>
> Hi,
>
> It should'nt be case sensitive, and the 'G10' amended to 'D2' certainly
> isn't case affected.
>
> I did miss the ! from REPLENISH! and also got the count wrong, where 2,
> 3 & 4 should have been 3, 4 & 5
>
> amended to column D from row 2 is:
>
>
> =IF(D2<>"replenish!,""",IF(INDIRECT(TEXT(NOW()-1,"yyyy")&TEXT(NOW()-1,"mm")&TEXT(NOW()-1,"dd")&"!d2")<>"replenish!",1,IF(INDIRECT(TEXT(NOW()-2,"yyyy")&TEXT(NOW()-2,"mm")&TEXT(NOW()-2,"dd")&"!d2")<>"replenish!",2,IF(INDIRECT(TEXT(NOW()-3,"yyyy")&TEXT(NOW()-3,"mm")&TEXT(NOW()-3,"dd")&"!d2")<>"replenish!",3,IF(INDIRECT(TEXT(NOW()-4,"yyyy")&TEXT(NOW()-4,"mm")&TEXT(NOW()-4,"dd")&"!d2")<>"replenish!",4,5)))))
>
>
> hope this helps
>
> blackgold21 Wrote:
> > I applied Bryan's formula to my workbook in the appropriate column
> > (cells
> > E2-E5 in all three worksheets), where the range B2-E5 looks like this:
> >
> > MIN ORQ ON HAND ACTION R.L.T.
> > 90 160 No Action 1
> > 44 12 REPLENISH! 1
> > 2000 2100 No Action 1
> > 1200 24 REPLENISH! 1
> >
> > I customized Bryan's formula to match my spreadsheet architecture,
> > somewhat.
> > Instead of the destination G10, (see his formula below) I applied the
> > appropriate cell address E2-E5 on all worksheets. The only product
> > the
> > formula returned for me is 1. I see now that when I substituted new
> > cell
> > addresses for the address in his formula, I inadvertantly substituted
> > upper-case letters for lower-case ones. Is that the root cause of the
> > problem???
> >
> > ANY IDEAS?
> >
> >
> > "Bryan Hessey" wrote:
> >
> > >
> > > Assuming the 'replenish' word is in colmn G, then for row 10 the
> > formula
> > >
> > >
> > >
> > >
> > =IF(G10<>"replenish,""",IF(INDIRECT(TEXT(NOW()-1,"yyyy")&TEXT(NOW()-1,"mm")&TEXT(NOW()-1,"dd")&"!g10")<>"replenish",1,IF(INDIRECT(TEXT(NOW()-2,"yyyy")&TEXT(NOW()-2,"mm")&TEXT(NOW()-2,"dd")&"!g10")<>"replenish",2,IF(INDIRECT(TEXT(NOW()-3,"yyyy")&TEXT(NOW()-3,"mm")&TEXT(NOW()-3,"dd")&"!g10")<>"replenish",2,IF(INDIRECT(TEXT(NOW()-4,"yyyy")&TEXT(NOW()-4,"mm")&TEXT(NOW()-4,"dd")&"!g10")<>"replenish",3,4)))))
> > >
> > > works for today and the prior 4 days, but stops counting on the
> > first
> > > non-'replenish' day.
> > >
> > >
> > >
> > >
> > >
> > > blackgold21 Wrote:
> > > > Thanks for your speedy reply to let me know you got the example and
> > are
> > > > thinking things over!
> > > >
> > > > "paul" wrote:
> > > >
> > > > > that is a very good response.The fact that your range is staic
> > makes
> > > > it
> > > > > easier..............<thinks>
> > > > > --
> > > > > paul
> > > > > remove nospam for email addy!
> > > > >
> > > > >
> > > > >
> > > > > "blackgold21" wrote:
> > > > >
> > > > > > Paul,
> > > > > >
> > > > > > The list of parts is static (it is a standing inventory of
> > parts
> > > > for which I
> > > > > > have an agreement with vendors to replenish when needed); only
> > the
> > > > status is
> > > > > > dynamic. Therefore each day's worksheet has pretty much the
> > same
> > > > data range.
> > > > > > Worksheets are named for days of the year (i.e., 20050824,
> > > > 200050825,
> > > > > > 20050826, etc.)
> > > > > > As for examples of data, here is a try for a data range of the
> > same
> > > > 4
> > > > > > columns x 5 rows on each worksheet. Column header "MIN ORQ"
> > > > represents
> > > > > > (Minimum Onhand Requirement). "R.L.T." represents
> > (Replenishment
> > > > Lag Time,
> > > > > > the formula I need help with):
> > > > > >
> > > > > > (sheet 20050824)
> > > > > > MIN ORQ ON HAND ACTION R. L. T.
> > > > > > 90 6 REPLENISH!
> > > > > > 44 90 No Action
> > > > > > 2000 907 REPLENISH!
> > > > > > 1200 2122 No Action
> > > > > >
> > > > > > (sheet 20050825)
> > > > > > MIN ORQ ON HAND ACTION R. L. T.
> > > > > > 90 6 REPLENISH!
> > > > > > 44 20 REPLENISH!
> > > > > > 2000 907 REPLENISH!
> > > > > > 1200 2000 No Action
> > > > > >
> > > > > > (sheet 20050826)
> > > > > > MIN ORQ ON HAND ACTION R. L. T.
> > > > > > 90 160 No Action
> > > > > > 44 12 REPLENISH!
> > > > > > 2000 2100 No Action
> > > > > > 1200 24 REPLENISH!
> > > > > >
> > > > > >
> > > > > > "paul" wrote:
> > > > > >
> > > > > > > each days workbook has different data?If the same part number
> > is
> > > > replenished
> > > > > > > they will be in a different cell on each day?.Each day has a
> > > > completely
> > > > > > > different sized data range?,How is each worksheet named?Give
> > us a
> > > > couple of
> > > > > > > examples of data,say the position of the example part number
> > that
> > > > did need
> > > > > > > replenishing say three days in a row,where is it on the
> > sheet,how
> > > > is the dta
> > > > > > > arranged what does the surrounding data look like?
> > > > > > > --
> > > > > > > paul
> > > > > > > remove nospam for email addy!
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > "blackgold21" wrote:
> > > > > > >
> > > > > > > > WOULD ANYONE ELSE CARE TO TAKE A STAB AT THIS
> > > > QUESTION--PLEASE??
> > > > > > > >
> > > > > > > > "blackgold21" wrote:
> > > > > > > >
> > > > > > > > > Sorry if my description was unclear. Each day's report
> > > > becomes a new sheet
> > > > > > > > > in the same workbook
> > > > > > > > >
> > > > > > > > > "Bernard Liengme" wrote:
> > > > > > > > >
> > > > > > > > > > Would be nice if you told us more about the workbook.
> > Like
> > > > were is the data
> > > > > > > > > > for each day of the week? - on the same worksheet or on
> > its
> > > > own worksheet.
> > > > > > > > > > Tell us what we need to help you.
> > > > > > > > > > --
> > > > > > > > > > Bernard V Liengme
> > > > > > > > > > www.stfx.ca/people/bliengme
> > > > > > > > > > remove caps from email
> > > > > > > > > >
> > > > > > > > > > "blackgold21" <blackgold21@discussions.microsoft.com>
> > wrote
> > > > in message
> > > > > > > > > >
> > news:BD934AFF-34D0-45DB-9F30-20172EFBAA64@microsoft.com...
> > > > > > > > > > >I have built a workbook in which I have inserted a
> > formula
> > > > to tell me
> > > > > > > > > > >whether
> > > > > > > > > > > the contents of a supply bin needs replenishment or
> > not.
> > > > The formula I
> > > > > > > > > > > used
> > > > > > > > > > > is: =IF(E3>F3,"REPLENISH!","No Action"). Each
> > morning, I
> > > > run a report to
> > > > > > > > > > > see
> > > > > > > > > > > what parts have been used, which becomes a new sheet
> > in
> > > > the workbook.
> > > > > > > > > > >
> > > > > > > > > > > Now, I want to add a formula that, whenever it sees
> > > > "REPLENISH!," it will
> > > > > > > > > > > back through the workbook to count whether that same
> > part
> > > > needed
> > > > > > > > > > > replenishment on consecutive previous days. If it
> > has,
> > > > then the latest
> > > > > > > > > > > worksheet will report the number of days that part
> > that
> > > > part has been in
> > > > > > > > > > > need
> > > > > > > > > > > of replenishment.
> > > > > > > > > > >
> > > > > > > > > > > Can you help me?
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > >
> > >
> > > --
> > > 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
> > >
> > >
>
>
> --
> 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
>
>

[paul]

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
> Bryan.Hessey@remove.removeMinterEllison.com 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:
> >
> > >
> > > Hi,
> > >
> > > It should'nt be case sensitive, and the 'G10' amended to 'D2'
> > certainly
> > > isn't case affected.
> > >
> > > I did miss the ! from REPLENISH! and also got the count wrong, where
> > 2,
> > > 3 & 4 should have been 3, 4 & 5
> > >
> > > amended to column D from row 2 is:
> > >
> > >
> > >
> > =IF(D2<>"replenish!,""",IF(INDIRECT(TEXT(NOW()-1,"yyyy")&TEXT(NOW()-1,"mm")&TEXT(NOW()-1,"dd")&"!d2")<>"replenish!",1,IF(INDIRECT(TEXT(NOW()-2,"yyyy")&TEXT(NOW()-2,"mm")&TEXT(NOW()-2,"dd")&"!d2")<>"replenish!",2,IF(INDIRECT(TEXT(NOW()-3,"yyyy")&TEXT(NOW()-3,"mm")&TEXT(NOW()-3,"dd")&"!d2")<>"replenish!",3,IF(INDIRECT(TEXT(NOW()-4,"yyyy")&TEXT(NOW()-4,"mm")&TEXT(NOW()-4,"dd")&"!d2")<>"replenish!",4,5)))))
> > >
> > >
> > > hope this helps
> > >
> > > blackgold21 Wrote:
> > > > I applied Bryan's formula to my workbook in the appropriate column
> > > > (cells
> > > > E2-E5 in all three worksheets), where the range B2-E5 looks like
> > this:
> > > >
> > > > MIN ORQ ON HAND ACTION R.L.T.
> > > > 90 160 No Action 1
> > > > 44 12 REPLENISH! 1
> > > > 2000 2100 No Action 1
> > > > 1200 24 REPLENISH! 1
> > > >
> > > > I customized Bryan's formula to match my spreadsheet architecture,
> > > > somewhat.
> > > > Instead of the destination G10, (see his formula below) I applied
> > the
> > > > appropriate cell address E2-E5 on all worksheets. The only
> > product
> > > > the
> > > > formula returned for me is 1. I see now that when I substituted
> > new
> > > > cell
> > > > addresses for the address in his formula, I inadvertantly
> > substituted
> > > > upper-case letters for lower-case ones. Is that the root cause of
> > the
> > > > problem???
> > > >
> > > > ANY IDEAS?
> > > >
> > > >
> > > > "Bryan Hessey" wrote:
> > > >
> > > > >
> > > > > Assuming the 'replenish' word is in colmn G, then for row 10 the
> > > > formula
> > > > >
> > > > >
> > > > >
> > > > >
> > > >
> > =IF(G10<>"replenish,""",IF(INDIRECT(TEXT(NOW()-1,"yyyy")&TEXT(NOW()-1,"mm")&TEXT(NOW()-1,"dd")&"!g10")<>"replenish",1,IF(INDIRECT(TEXT(NOW()-2,"yyyy")&TEXT(NOW()-2,"mm")&TEXT(NOW()-2,"dd")&"!g10")<>"replenish",2,IF(INDIRECT(TEXT(NOW()-3,"yyyy")&TEXT(NOW()-3,"mm")&TEXT(NOW()-3,"dd")&"!g10")<>"replenish",2,IF(INDIRECT(TEXT(NOW()-4,"yyyy")&TEXT(NOW()-4,"mm")&TEXT(NOW()-4,"dd")&"!g10")<>"replenish",3,4)))))
> > > > >
> > > > > works for today and the prior 4 days, but stops counting on the
> > > > first
> > > > > non-'replenish' day.
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > blackgold21 Wrote:
> > > > > > Thanks for your speedy reply to let me know you got the example
> > and
> > > > are
> > > > > > thinking things over!
> > > > > >
> > > > > > "paul" wrote:
> > > > > >
> > > > > > > that is a very good response.The fact that your range is
> > staic
> > > > makes
> > > > > > it
> > > > > > > easier..............<thinks>
> > > > > > > --
> > > > > > > paul
> > > > > > > remove nospam for email addy!
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > "blackgold21" wrote:
> > > > > > >
> > > > > > > > Paul,
> > > > > > > >
> > > > > > > > The list of parts is static (it is a standing inventory of
> > > > parts
> > > > > > for which I
> > > > > > > > have an agreement with vendors to replenish when needed);
> > only
> > > > the
> > > > > > status is
> > > > > > > > dynamic. Therefore each day's worksheet has pretty much
> > the
> > > > same
> > > > > > data range.
> > > > > > > > Worksheets are named for days of the year (i.e.,
> > 20050824,
> > > > > > 200050825,
> > > > > > > > 20050826, etc.)
> > > > > > > > As for examples of data, here is a try for a data range of
> > the
> > > > same
> > > > > > 4
> > > > > > > > columns x 5 rows on each worksheet. Column header "MIN
> > ORQ"
> > > > > > represents
> > > > > > > > (Minimum Onhand Requirement). "R.L.T." represents
> > > > (Replenishment
> > > > > > Lag Time,
> > > > > > > > the formula I need help with):
> > > > > > > >
> > > > > > > > (sheet 20050824)
> > > > > > > > MIN ORQ ON HAND ACTION R. L. T.
> > > > > > > > 90 6 REPLENISH!
> > > > > > > > 44 90 No Action
> > > > > > > > 2000 907 REPLENISH!
> > > > > > > > 1200 2122 No Action
> > > > > > > >
> > > > > > > > (sheet 20050825)
> > > > > > > > MIN ORQ ON HAND ACTION R. L. T.
> > > > > > > > 90 6 REPLENISH!
> > > > > > > > 44 20 REPLENISH!
> > > > > > > > 2000 907 REPLENISH!
> > > > > > > > 1200 2000 No Action
> > > > > > > >
> > > > > > > > (sheet 20050826)
> > > > > > > > MIN ORQ ON HAND ACTION R. L. T.
> > > > > > > > 90 160 No Action
> > > > > > > > 44 12 REPLENISH!
> > > > > > > > 2000 2100 No Action
> > > > > > > > 1200 24 REPLENISH!
> > > > > > > >
> > > > > > > >
> > > > > > > > "paul" wrote:
> > > > > > > >
> > > > > > > > > each days workbook has different data?If the same part
> > number
> > > > is
> > > > > > replenished
> > > > > > > > > they will be in a different cell on each day?.Each day
> > has a
> > > > > > completely
> > > > > > > > > different sized data range?,How is each worksheet
> > named?Give
> > > > us a
> > > > > > couple of
> > > > > > > > > examples of data,say the position of the example part
> > number
> > > > that
> > > > > > did need
> > > > > > > > > replenishing say three days in a row,where is it on the
> > > > sheet,how
> > > > > > is the dta
> > > > > > > > > arranged what does the surrounding data look like?
> > > > > > > > > --
> > > > > > > > > paul
> > > > > > > > > remove nospam for email addy!
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > "blackgold21" wrote:
> > > > > > > > >
> > > > > > > > > > WOULD ANYONE ELSE CARE TO TAKE A STAB AT THIS
> > > > > > QUESTION--PLEASE??
> > > > > > > > > >
> > > > > > > > > > "blackgold21" wrote:
> > > > > > > > > >
> > > > > > > > > > > Sorry if my description was unclear. Each day's
> > report
> > > > > > becomes a new sheet
> > > > > > > > > > > in the same workbook
> > > > > > > > > > >
> > > > > > > > > > > "Bernard Liengme" wrote:
> > > > > > > > > > >
> > > > > > > > > > > > Would be nice if you told us more about the
> > workbook.
> > > > Like
> > > > > > were is the data
> > > > > > > > > > > > for each day of the week? - on the same worksheet
> > or on
> > > > its
> > > > > > own worksheet.
> > > > > > > > > > > > Tell us what we need to help you.
> > > > > > > > > > > > --
> > > > > > > > > > > > Bernard V Liengme
> > > > > > > > > > > > www.stfx.ca/people/bliengme
> > > > > > > > > > > > remove caps from email
> > > > > > > > > > > >
> > > > > > > > > > > > "blackgold21"
> > <blackgold21@discussions.microsoft.com>
> > > > wrote
> > > > > > in message
> > > > > > > > > > > >
> > > > news:BD934AFF-34D0-45DB-9F30-20172EFBAA64@microsoft.com...
> > > > > > > > > > > > >I have built a workbook in which I have inserted
> > a
> > > > formula
> > > > > > to tell me
> > > > > > > > > > > > >whether
> > > > > > > > > > > > > the contents of a supply bin needs replenishment
> > or
> > > > not.
> > > > > > The formula I
> > > > > > > > > > > > > used
> > > > > > > > > > > > > is: =IF(E3>F3,"REPLENISH!","No Action"). Each
> > > > morning, I
> > > > > > run a report to
> > > > > > > > > > > > > see
> > > > > > > > > > > > > what parts have been used, which becomes a new
> > sheet
> > > > in
> > > > > > the workbook.
> > > > > > > > > > > > >
> > > > > > > > > > > > > Now, I want to add a formula that, whenever it
> > sees
> > > > > > "REPLENISH!," it will
> > > > > > > > > > > > > back through the workbook to count whether that
> > same
> > > > part
> > > > > > needed
> > > > > > > > > > > > > replenishment on consecutive previous days. If
> > it
> > > > has,
> > > > > > then the latest
> > > > > > > > > > > > > worksheet will report the number of days that
> > part
> > > > that
> > > > > > part has been in
> > > > > > > > > > > > > need
> > > > > > > > > > > > > of replenishment.
> > > > > > > > > > > > >
> > > > > > > > > > > > > Can you help me?
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > >
> > > > >
> > > > > --
> > > > > 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
> > > > >
> > > > >
> > >
> > >
> > > --
> > > 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
> > >
> > >
>
>
> --
> 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
>
>

[Bryan Hessey]

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 . . . .


** Followup to this, the formula

=IF(D2<>"replenish!","",IF(INDIRECT(TEXT(G$1,"yyyy")&TEXT(G$1,"mm")&TEXT(G$1,"dd")&"!D"&(ROW()))<>"replenish!",1,IF(INDIRECT(TEXT(G$2,"yyyy")&TEXT(G$2,"mm")&TEXT(G$2,"dd")&"!d"&(ROW()))<>"replenish!",2,IF(INDIRECT(TEXT(G$3,"yyyy")&TEXT(G$3,"mm")&TEXT(G$3,"dd")&"!d"&(ROW()))<>"replenish!",3,IF(INDIRECT(TEXT(G$4,"yyyy")&TEXT(G$4,"mm")&TEXT(G$4,"dd")&"!d"&(ROW()))<>"replenish!",4,5)))))

was fixed by email and the working book is now with the OP

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:
[color=blue]
>
> 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
> Bryan.Hessey@remove.removeMinterEllison.com 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
>
>

[paul]

yes my first attempt was a series of lookups and the results were
concatenated so in a column to the right i had
replenishreplenishnoactionreplenish,the weekends are another wrinkle.Praps
some code mght be easier!
--
paul
remove nospam for email addy!



"Bryan Hessey" wrote:
[color=blue]
>
> 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:
> > 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
> > > Bryan.Hessey@remove.removeMinterEllison.com 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
>
>

[paul]

my date ref cell and my target cell for replenish and noaction are slightly
different but this worked for me
=IF(F2<>"replenish","",IF(INDIRECT(TEXT(J2-1,"yyyy")&TEXT(J2-1,"mm")&TEXT(J2-1,"dd")&"!F2")<>"replenish",1,IF(INDIRECT(TEXT(J2-2,"yyyy")&TEXT(J2-2,"mm")&TEXT(J2-2,"dd")&"!F2")<>"replenish",2,IF(INDIRECT(TEXT(J2-3,"yyyy")&TEXT(J2-3,"mm")&TEXT(J2-3,"dd")&"!F2")<>"replenish",3,IF(INDIRECT(TEXT(J2-4,"yyyy")&TEXT(J2-4,"mm")&TEXT(J2-4,"dd")&"!F2")<>"replenish",4,"MORE")))))

you had a slight typing error at the first "replenish!,""" Bryan,.....I also
dont use the !



--
paul
remove nospam for email addy!



"paul" wrote:
[color=blue]
> yes my first attempt was a series of lookups and the results were
> concatenated so in a column to the right i had
> replenishreplenishnoactionreplenish,the weekends are another wrinkle.Praps
> some code mght be easier!
> --
> paul
> remove nospam for email addy!
>
>
>
> "Bryan Hessey" wrote:
>
> >
> > 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:
> > > 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
> > > > Bryan.Hessey@remove.removeMinterEllison.com 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
> >
> >

[Bryan Hessey]

Paul,

I did detect that error, with the misplaced ," and corrected it, also, because of the Saturday, Sunday and holidays situation decided to use 4 cells, G1 to G4 to hold the date of the sheets last saved (in reverse order, newest in G1), thus in the OP's sheet the formula:

=IF(D2<>"replenish!","",IF(INDIRECT(TEXT(G$1,"yyyy")&TEXT(G$1,"mm")&TEXT(G$1,"dd")&"!D"&(ROW()))<>"replenish!",1,IF(INDIRECT(TEXT(G$2,"yyyy")&TEXT(G$2,"mm")&TEXT(G$2,"dd")&"!d"&(ROW()))<>"replenish!",2,IF(INDIRECT(TEXT(G$3,"yyyy")&TEXT(G$3,"mm")&TEXT(G$3,"dd")&"!d"&(ROW()))<>"replenish!",3,IF(INDIRECT(TEXT(G$4,"yyyy")&TEXT(G$4,"mm")&TEXT(G$4,"dd")&"!d"&(ROW()))<>"replenish!",4,5)))))

was used, and the various numbers seemed to fair well for a good result for the OP. The word used was 'REPLENISH!' and the sheet was returned by email about 5 hours ago - hopefully all will be well.

Cheers

my date ref cell and my target cell for replenish and noaction are slightly
different but this worked for me
=IF(F2<>"replenish","",IF(INDIRECT(TEXT(J2-1,"yyyy")&TEXT(J2-1,"mm")&TEXT(J2-1,"dd")&"!F2")<>"replenish",1,IF(INDIRECT(TEXT(J2-2,"yyyy")&TEXT(J2-2,"mm")&TEXT(J2-2,"dd")&"!F2")<>"replenish",2,IF(INDIRECT(TEXT(J2-3,"yyyy")&TEXT(J2-3,"mm")&TEXT(J2-3,"dd")&"!F2")<>"replenish",3,IF(INDIRECT(TEXT(J2-4,"yyyy")&TEXT(J2-4,"mm")&TEXT(J2-4,"dd")&"!F2")<>"replenish",4,"MORE")))))

you had a slight typing error at the first "replenish!,""" Bryan,.....I also
dont use the !



--
paul
remove nospam for email addy!



"paul" wrote:
[color=blue]
> yes my first attempt was a series of lookups and the results were
> concatenated so in a column to the right i had
> replenishreplenishnoactionreplenish,the weekends are another wrinkle.Praps
> some code mght be easier!
> --
> paul
> remove nospam for email addy!
>
>
>
> "Bryan Hessey" wrote:
>
> >
> > 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:
> > > 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
> > > > Bryan.Hessey@remove.removeMinterEllison.com 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
> >
> >