Formula Question #18

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?
0
8/26/2005 6:49:55 PM
excel.misc 78881 articles. 5 followers. Follow

17 Replies
472 Views

Similar Articles

[PageSpeed] 13

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? 


0
bliengme5824 (3040)
8/26/2005 7:12:20 PM
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? 
> 
> 
> 
0
8/26/2005 8:13:01 PM
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? 
> > 
> > 
> > 
0
8/26/2005 11:45:01 PM
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? 
> > > 
> > > 
> > > 
0
8/27/2005 12:40:54 AM
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? 
> > > > 
> > > > 
> > > > 
0
8/27/2005 3:24:05 AM
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? 
> > > > > 
> > > > > 
> > > > > 
0
8/27/2005 4:05:10 AM
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? 
> > > > > > 
> > > > > > 
> > > > > > 
0
8/27/2005 4:12:02 AM
Assuming the 'replenish' word is in colmn G, then for row 10 the formul



=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 firs
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 make
> it
> > easier..............<thinks>
> > --
> > paul
> > remove nospam for email addy!
> >
> >
> >
> > "blackgold21" wrote:
> >
> > > Paul,
> > >
> > > The list of parts is static (it is a standing inventory of part
> for which I
> > > have an agreement with vendors to replenish when needed); only th
> status is
> > > dynamic.  Therefore each day's worksheet has pretty much the sam
> 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 sam
> 4
> > > columns x 5 rows on each worksheet.  Column header "MIN ORQ
> represents
> > > (Minimum Onhand Requirement).  "R.L.T." represents (Replenishmen
> 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 i
> replenished
> > > > they will be in a different cell on each day?.Each day has 
> completely
> > > > different sized data range?,How is each worksheet named?Give us 
> couple of
> > > > examples of data,say the position of the example part number tha
> did need
> > > > replenishing say three days in a row,where is it on the sheet,ho
> 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 THI
> QUESTION--PLEASE??
> > > > >
> > > > > "blackgold21" wrote:
> > > > >
> > > > > > Sorry if my description was unclear.  Each day's repor
> becomes a new sheet
> > > > > > in the same workbook
> > > > > >
> > > > > > "Bernard Liengme" wrote:
> > > > > >
> > > > > > > Would be nice if you told us more about the workbook. Lik
> were is the data
> > > > > > > for each day of the week? - on the same worksheet or on it
> 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> wrot
> in message
> > > > > > > news:BD934AFF-34D0-45DB-9F30-20172EFBAA64@microsoft.com...
> > > > > > > >I have built a workbook in which I have inserted a formul
> 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, 
> run a report to
> > > > > > > > see
> > > > > > > > what parts have been used, which becomes a new sheet i
> 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?action=getinfo&userid=21059
View this thread: http://www.excelforum.com/showthread.php?threadid=399574

0
8/27/2005 7:01:20 AM
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?action=getinfo&userid=21059
> View this thread: http://www.excelforum.com/showthread.php?threadid=399574
> 
> 
0
8/27/2005 8:18:08 PM
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?action=getinfo&userid=21059
> > View this thread:
> http://www.excelforum.com/showthread.php?threadid=399574
> >
> >


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

0
8/27/2005 10:06:34 PM
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?action=getinfo&userid=21059
> > > View this thread:
> > http://www.excelforum.com/showthread.php?threadid=399574
> > >
> > >
> 
> 
> -- 
> Bryan Hessey
> ------------------------------------------------------------------------
> Bryan Hessey's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=21059
> View this thread: http://www.excelforum.com/showthread.php?threadid=399574
> 
> 
0
8/27/2005 11:40:02 PM
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?action=getinfo&userid=21059
> > > > View this thread:
> > > http://www.excelforum.com/showthread.php?threadid=399574
> > > >
> > > >
> >
> >
> > --
> > Bryan Hessey
> >
> ------------------------------------------------------------------------
> > Bryan Hessey's Profile:
> http://www.excelforum.com/member.php?action=getinfo&userid=21059
> > View this thread:
> http://www.excelforum.com/showthread.php?threadid=399574
> >
> >


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

0
8/28/2005 12:24:29 AM
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?action=getinfo&userid=21059
> > > > > View this thread:
> > > > http://www.excelforum.com/showthread.php?threadid=399574
> > > > >
> > > > >
> > >
> > >
> > > --
> > > Bryan Hessey
> > >
> > ------------------------------------------------------------------------
> > > Bryan Hessey's Profile:
> > http://www.excelforum.com/member.php?action=getinfo&userid=21059
> > > View this thread:
> > http://www.excelforum.com/showthread.php?threadid=399574
> > >
> > >
> 
> 
> -- 
> Bryan Hessey
> ------------------------------------------------------------------------
> Bryan Hessey's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=21059
> View this thread: http://www.excelforum.com/showthread.php?threadid=399574
> 
> 
0
8/28/2005 3:22:01 AM
Paul,

I may yet have to resort to 'helper' cells, as on reflection I thin
that there will be sheets for Monday to Friday (except publi
holidays), and the only way that the sheetname will be detectable is b
noting the last 5 (or so) sheet dates which can then be utilised in th
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 th
limit of '7 nested' that Excel has, however, I think a score of 5 woul
be sufficient for the OP's needs.

The formula may also need to be adjusted to show how many of the last 
days, rather than the currently selected 'consecutive-working-backwards
approach, but this will depend on requirements, and 'consecutive' look
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 o
> the
> no action/replenish result,and then i was going to sum over th
> 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 figur
> 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 an
> after
> > that 20050826 for you to get more than 1
> >
> > If your sheets are not that, then you need specify a date and us
> 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:[color=green]
> > > Bryan,
> > >
> > > Hey.  I'm still getting 1's, I'm sorry to say.  (Looks like you'v
> done
> > > so
> > > much work to help.)
> > >
> > > I just wonder if my poor explanation is not the cause of th
> trouble.
> > > Is
> > > there any way I can post the workbook?
> > >
> > > "Bryan Hessey" wrote:
> 
> 
> ------------------------------------------------------------------------
> > Bryan Hessey's Profile
> http://www.excelforum.com/member.php?action=getinfo&userid=21059
> > View this thread
> http://www.excelforum.com/showthread.php?threadid=399574
> >
> 

--
Bryan Hesse
-----------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2105
View this thread: http://www.excelforum.com/showthread.php?threadid=39957

0
8/28/2005 4:34:41 AM
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:[color=green]
> > > > 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?action=getinfo&userid=21059
> > > View this thread:
> > http://www.excelforum.com/showthread.php?threadid=399574
> > >
> > >
> 
> 
> -- 
> Bryan Hessey
> ------------------------------------------------------------------------
> Bryan Hessey's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=21059
> View this thread: http://www.excelforum.com/showthread.php?threadid=399574
> 
> 
0
8/28/2005 6:14:02 AM
my date ref cell and my target cell for replenish and noaction are slightly 
different but this worked for m
=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:

> 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:[color=green]
> > > > > 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?action=getinfo&userid=21059
> > > > View this thread:
> > > http://www.excelforum.com/showthread.php?threadid=399574
> > > >
> > > >
> > 
> > 
> > -- 
> > Bryan Hessey
> > ------------------------------------------------------------------------
> > Bryan Hessey's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=21059
> > View this thread: http://www.excelforum.com/showthread.php?threadid=399574
> > 
> > 
0
8/28/2005 8:51:01 AM
Paul,

I did detect that error, with the misplaced ," and corrected it, also
because of the Saturday, Sunday and holidays situation decided to use 
cells, G1 to G4 to hold the date of the sheets last saved (in revers
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 goo
result for the OP. The word used was 'REPLENISH!' and the sheet wa
returned by email about 5 hours ago - hopefully all will be well.

Cheers


paul Wrote:
> my date ref cell and my target cell for replenish and noaction ar
> 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,.....
> also
> dont use the !
> 
> 
> 
> --
> paul
> remove nospam for email addy!
> 
> 
> 
> "paul" wrote:
> 
> > 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 anothe
> 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 
> think
> > > that there will be sheets for Monday to Friday (except public
> > > holidays), and the only way that the sheetname will be detectabl
> is by
> > > noting the last 5 (or so) sheet dates which can then be utilised i
> 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 t
> the
> > > limit of '7 nested' that Excel has, however, I think a score of 
> would
> > > be sufficient for the OP's needs.
> > >
> > > The formula may also need to be adjusted to show how many of th
> last 5
> > > days, rather than the currently selecte
> '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 diplaye
> 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 t
> 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 ma
> not
> > > > > (yet) be the case. Your next sheet should be named 2005082
> and
> > > > after
> > > > > that 20050826 for you to get more than 1
> > > > >
> > > > > If your sheets are not that, then you need specify a date an
> use
> > > > that
> > > > > reference in the formula, thus if I enter a date (say Friday'
> date,
> > > > > because I don't work Saturday nor Sunday) in cell J5 I woul
> 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:[color=green]
> > > > > > 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?action=getinfo&userid=21059
> > > > > View this thread:
> > > > http://www.excelforum.com/showthread.php?threadid=399574
> > > > >
> > > > >
> > >
> > >
> > > --
> > > Bryan Hessey
> > >
> ------------------------------------------------------------------------
> > > Bryan Hessey's Profile:
> http://www.excelforum.com/member.php?action=getinfo&userid=21059
> > > View this thread:
> http://www.excelforum.com/showthread.php?threadid=399574
> > >
> > >


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

0
8/28/2005 11:05:27 AM
Reply:

Similar Artilces: