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
440 Views

Similar Articles

[PageSpeed] 36

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:

Exchange 2003 Question #3
Dear all, I have upgraded from Exchange 5.5 and notice that I miss the feature where I can clean/delete mail based on certain criteria from users mailbox via the System Manager. Is this feature still available in Exchange 2003, If so, where is it? Thanks. Regards, Kueh. You can use ExMerge to remove emails based on certain criteria (ie. attachment name, subject, etc). http://www.msexchange.org/tutorials/MF013.html "KA Kueh" wrote: > Dear all, > > I have upgraded from Exchange 5.5 and notice that I miss the feature where I > can clean/delete mail based on ...

Combining cells to create a formula
Hi, I have two cells that I want to combine to have a working formula B1 = sum B2 = d3 b3 = =b1&"("&b2&")" D3 = 10 The result in b3 is : sum(d3) How do I get this to result in the actual value in D3. I know I can simply write =D3, but the actual reason for combining the two cells is more complicated than explained here. Thanks Hi I think you are perhaps looking for the Indirect function =INDIRECT("D"&ROW(3:3)) would return the value contained in D3 As you copy down, the formula would alter to 4, 5 etc. represent D4, D5 etc. -- Regards R...

Question On Fields
What is the (Microsoft desciption)difference between the SIC field and the Industry Code field? To me they seem like a duplication. Thanks! Shauna Hi, According to the Deployment Manager descriptions of attributes: SIC - Standard Industrial Classification code for the Account industrycode - the type of industry with which the account is associated... Hope this helps! "Shauna Koppang" <anonymous@discussions.microsoft.com> wrote in message news:034101c48af4$62524a70$a401280a@phx.gbl... > What is the (Microsoft desciption)difference between the > SIC field and the...

in excel how can we put formula to convert numericalfigureto word
for example : in excel i have mention 25000.00 in numerical amount , i want to know how can i convert in next colum , about word ?/; How can i put formula to make the numerical in to words like 25000 in numerical to twenty five thousands in word. There is no direct functions to convert this. For a VBA solution check out the below links http://support.microsoft.com/kb/213360 http://www.ozgrid.com/VBA/ValueToWords.htm http://www.xldynamic.com/source/xld.xlFAQ0004.html -- Jacob (MVP - Excel) "excel" wrote: > for example : > > in excel i hav...

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

Move/Copy A Row Based on Formulas to a New Worksheet
I want to move several rows of sub-totals (averages within sub-groups) to a summary worksheet, but I get the Ref error. How can I copy sub-group averages to another worksheet? Thank you. high light and copy. select where you want it. edit>paste special>values. this will turn you formulas into hard numbers. you are getting the #Ref error because on the other sheet where you pasted the formulas, the formula no longer had the same references that they had on the other sheet. for example: =sum(a1:a10) in cell a11 you copy and paste on another sheet at cell a1. excell tries to compensat...

Erase data, preserve formula's
I have a an excel file with 12 worksheets for the financial year and an additional worksheet for yearly totals. I need to get a blank copy of this and was wondering if anyone knew a way to delete all the user inputted data while keeping the formatting and formula's intact. Any help is much appreciated. -- urbanfox ------------------------------------------------------------------------ urbanfox's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=22826 View this thread: http://www.excelforum.com/showthread.php?threadid=519004 Hi Hit F5 and select Special a...

How do I set up a formula in excel that is the tenth root of 7 ve.
How do I set up a formula in excel that is the tenth root of 7 versus the square root of 7? try the formula =7^(1/10). HTH Alan P. "kestig1" <kestig1@discussions.microsoft.com> wrote in message news:99C16AB7-8BD6-4671-B138-5F2961E46067@microsoft.com... > How do I set up a formula in excel that is the tenth root of 7 versus the > square root of 7? ...

Need help with formula 01-13-10
I am trying to adapt a formula in I2 from another spreadsheet that works well, but won't in mine. I've traced the error, but I would need help to understand the help it gives! My formula is this: =IF(J2="0-Jan-00","To be advised",WORKDAY(J2,1,NWD)). I have a worksheet in the same workbook with a list of non-workdays, and defined the column of dates with the name "NWD". What I expect the formula to do is this: If J2 is Feb. 4, it would give Feb. 5 in cell I2 because Feb. 5 is NOT a non-workday in NWD. But if J2 is Feb. 5, and Feb. 6 and...

Please help..with a formula. I don't know code.
I have a long list of numbers - values in a file X, and I want to fin and replace those values in a even larger list in a file Z an highlight those values in Z -- Message posted from http://www.ExcelForum.com Hi not really sure what you're trying to achieve. What do you want to replace, etc. You may give an example (plain text - no attachment please) >-----Original Message----- >I have a long list of numbers - values in a file X, and I want to find >and replace those values in a even larger list in a file Z and >highlight those values in Z. > > >--- >Message...

AD/Network design question
Hello all I have a 2 sites which users come and go from. These sites have 2 different network ip domains (192.168.1.x and 192.168.2.x). Users need to be authenticated using the same user id and password at both sites. DC/GC is at 192.168.1.x and is a SBS 2003. Server at 192.168.2.x is Server 2003. When the server at 192.168.2.x is connected via vpn to 192.168.1.x all is well. How do I get the server at 192.168.2.x to act as a AD/DC when it’s not connected to 192.168.1.x ? Thank you Hal I think you will run here into the limitations of SBS :-( @ SBS experts : ...

newbie question
Excel help says it should be a tab under tools, but I can't see it in my version. I would appreciate any help. Thanks. You must first load the Solver Add-in through Tools>Add-ins. Gord Dibben MS Excel MVP On Sat, 03 Nov 2007 07:46:34 -0700, analyst41@hotmail.com wrote: >Excel help says it should be a tab under tools, but I can't see it in >my version. > >I would appreciate any help. > >Thanks. On Nov 3, 10:56 am, Gord Dibben <gorddibbATshawDOTca> wrote: > You must first load the Solver Add-in through Tools>Add-ins. > > Gord Dibben MS E...

formula: counting presence
Hello! I have to count presence of employees from sheets between START and END, which is stored in G9 cell. I think it should be something like: =SUM(IF(START:END!G9="present"; 1; 0)), but this one returns #REF and I don't don't why. Try these from a post of mine today. One way. Put the sumif on each sheet with an indirect reference to d12 of the master. then use =sum(sheet1:sheet21!a2) where a2 in your sumif formula. One way to put=SUMIF(B:B,Sheet1!D12) on each sheet is to select all>type the formula in the cell desired>after the error msg>delete from the ...

Mail merge & staple question
Does anyone know how to use mail merge in Publisher with a printer that folds and staples?? Publisher is sending it to the printer as "one" big file and trying to staples "all" my newsletters together instead of individual ones with the addresses on them. Or if anyone has any other programs or ideas on how I can accomplish this task, sure would be appreciated. Thanks While waiting for decisions from his 6 university choices, Ed sees a message from Parishsecretary <Parishsecretary@discussions.microsoft.com>. On it is written: > Does anyone know how to use mai...

Help on Macro or Formula
Hi, i hope someone can help me. i need to create a formula that sits in a cell and looks for data. ( obvioiusly ). however, the formula needs to be in place even though the file from ehere the data comes from might not be there yet. ( i have to create a book that when a new file is created, the links are already in place ). i think it could work with an IF type formula for ( if B2="",""). here is my information. Cell description: A2 = Job no. B2 = Client Name D2 = Actual Spend on project Register!D2 = Job Description Register!H2 = Quoted Amount my path is S:\Clients\...

question about using the correct schema namespace
I have a Access/VBA client that exports xml to the local drive, then posts it over http to an aspx page. The aspx page consumes it, then builds itself based on the xml data. This works find only if I first mannually change the root entry's namespace url as shown below. Does anyone know how I can get the two (cleint xml export and aspx xml consume) to work together with the appropriate namespace? Here is the top three lines of my xml export (prior to mannually changing it): <?xml version="1.0" encoding="UTF-8"?> <root xmlns:xsd="http://www.w3.org/200...

Office 2010 Buying Question Assistance Needed
I've been looking through the MS Office 2010 web site to try to determine what my new small company would require, but I can't find the information I need. We for sure would need Office Pro Plus, but other than that I'm not sure. We want to run it on our own server. We will initially have 3-5 people using it and perhaps more later on. Would we need to purchase site licensing? Unfortunately, our programmers are MS haters (I'm not) and I can't get any assistance from them on this, but I have power of the pen. I would appreciate any assistance I can get. Th...

Formula for date field
1.I have simple dates in one column (say column A) . 2.In the next column(Column B) I would like the date five months after Column A to be displayed.Eg if Column A has an entry of 9th June 2007,Column B should display 8th November,2007. 3.A simple formula does not do the job as this does not take into account the different number of days in different months! regards S.Sanatani Your post is a bit ambiguous since you don't really say how the different number of days in months should be handled. One way: A1: <date> B1: =DATE(YEAR(A1),MONTH(A1)+5,DAY(A1)-1) In articl...

REQ: Can Someone Help Me With This Outlook XP Question?
Hello All: I use Word to edit my e-mail msgs in Outlook XP. I had to reinstall Office the other day and now whenever I want to start a new e-mail or reply to an e-mail I get a warning that comes up: "A program is trying to access e-mail addresses you have stored in Outlook. Do you want to allow this? If this is unexpected it may be a virus and you should choose 'No'" There is a box that asks for the amount of time to allow the access: 1 to 10 minutes. Do I have to have specific settings for my Outlook address book? I use the Contact area in Outlook for addresses. I have ...

Baseball Stats question: How can I get the RBI's?
I have a data table that looks like this: Code ------------------- STR-S SNK-S STR-K STR-F SNK-S STR-3 STR-2 STR-F SNK-S SNK-4 SNK-S STR-3 SNK-F STR-4 STR-O ------------------- "STR", etc at the beginning are pitch types, SNK is Sinker for example The end character is the result of the pitch, a 4 would be a home run, 3 a triple, and so forth. O is out, F is foul, S is strike --- you ge it. How can I figure the amount of RBI's? I know that it's 5, but I can' think of any automated way to calculate this, anyone got any ideas? THANK YOU!! :confused -- AVER...

Question About Missing Data
So, this is probably really easy, but I just want to ask and see if I may be missing something here. Some data on vendor numbers changed. Let’s say IBM used to have a vendor number 12345 and now it’s vendor number is 56789. I can identify IBM as IBM, but I really want to use the number, not the name. Should I set up a table that ties the numbers together, so that Access knows 12345 = 56789? Or, should I do some kind of Update Query and change all incidences of 12345 to be 56789? Or, is there some other, method, like a ‘best practices for missing data’? Thanks! Ryan--- ...

Copy/Paste formulas
How do I keep certain cells (those I want to point to a specific 'constant') from incrementing while the remaing cells in my formulas increment as expected. Example: ((E65)*(COUNTIF(I7:I7,"V"))) where the cell "E65" contains a set value that I want to be placed in the result as I step down the incremental (I) rows when the character "V" is found in the particular (I) row. When I do my copy and paste, the (E) row increments as the (I) rows increment. $E$65 "BobG" wrote: > How do I keep certain cells (those I want to point to a specific...

HOW DO I COPY FORMULA WITHOUT CHANGING CELL REFERENCE
My cell c1 contains the formula b1-a1.when i copy this formula to cells d1 and e1 the cells d1 and e1 have the following formula : d1=c1-b1 e1=d1-c1 but i want the following d1 should be b2-a2 and e2 should be b3-a3 how do i do this? One way... In C1 enter & copy across: =INDEX($A:$A,COLUMN()-COLUMN($C$1)+1)-INDEX($B:$B,COLUMN()-COLUMN($C$1)+1) anantth wrote: > My cell c1 contains the formula b1-a1.when i copy this formula to cells d1 > and e1 the cells d1 and e1 have the following formula : > d1=c1-b1 > e1=d1-c1 > > but i want the following > d1 should be b2-...

Getting Cell Value from the Concatenate formula
I built a concatenate formula that returns the following result: =Jul! $D27 I am looking for the cell contents of Sheet: July Column D Row 27. I tried to use offset, but I am stumped. Can I add something to the front of the concatenate to not only build the reference to the cell, but also return the value instead of the =Jul!$D27 ? Thanks John =indirect(yourformulahere) Don't include the equal sign in your formula. And match the name correctly (Jul or July???). Depending on the name of the worksheet, you may need to have a string that looks like: 'Sheet 99'!d27 =indirec...

Formula for competition timesheet
Here is the situation. I have a number of members in a clay target club who shoot a competition over a number of ranges. Ranges 1 to 8. They shoot a competition over 4 days. They start shooting at a specific time each day. Start time in cell A1. The duration of the time they spend on each range is specified in B1. These times may vary each day. I have set up a table in the worksheet that shows the squad numbers in column A, the ranges they shoot each day and the time they start to shoot on each range. This table only shows the squad numbers up to the number of members shooting, which is ...