|
Formula Question
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(E3F3,"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? |
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" wrote in message ... 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(E3F3,"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? |
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" wrote in message ... 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(E3F3,"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? |
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" wrote in message ... 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(E3F3,"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? |
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" wrote in message ... 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(E3F3,"REPLENISH!","No Action"). Each morning, I run a report to see what parts have been used, which becomes a new sheet in the workbook. Now, I want to add a formula that, whenever it sees "REPLENISH!," it will back through the workbook to count whether that same part needed replenishment on consecutive previous days. If it has, then the latest worksheet will report the number of days that part that part has been in need of replenishment. Can you help me? |
Paul,
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" wrote in message ... 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(E3F3,"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? |
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" wrote in message ... 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(E3F3,"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? |
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" wrote in message ... 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(E3F3,"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? |
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(NO W()-2,"yyyy")&TEXT(NOW()-2,"mm")&TEXT(NOW()-2,"dd")&"!g10")<"replenish",2,IF(INDIRECT(TEXT(NO W()-3,"yyyy")&TEXT(NOW()-3,"mm")&TEXT(NOW()-3,"dd")&"!g10")<"replenish",2,IF(INDIRECT(TEXT(NO W()-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" wrote in message ... 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(E3F3,"REPLENISH!","No Action"). Each morning, I run a report to see what parts have been used, which becomes a new sheet in the workbook. Now, I want to add a formula that, whenever it sees "REPLENISH!," it will back through the workbook to count whether that same part needed replenishment on consecutive previous days. If it has, then the latest worksheet will report the number of days that part that part has been in need of replenishment. Can you help me? -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=399574 |
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(NO W()-2,"yyyy")&TEXT(NOW()-2,"mm")&TEXT(NOW()-2,"dd")&"!g10")<"replenish",2,IF(INDIRECT(TEXT(NO W()-3,"yyyy")&TEXT(NOW()-3,"mm")&TEXT(NOW()-3,"dd")&"!g10")<"replenish",2,IF(INDIRECT(TEXT(NO W()-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" wrote in message ... 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(E3F3,"REPLENISH!","No Action"). Each morning, I run a report to see what parts have been used, which becomes a new sheet in the workbook. Now, I want to add a formula that, whenever it sees "REPLENISH!," it will back through the workbook to count whether that same part needed replenishment on consecutive previous days. If it has, then the latest worksheet will report the number of days that part that part has been in need of replenishment. Can you help me? -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=399574 |
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(NO W()-2,"yyyy")&TEXT(NOW()-2,"mm")&TEXT(NOW()-2,"dd")&"!d2")<"replenish!",2,IF(INDIRECT(TEXT(NO W()-3,"yyyy")&TEXT(NOW()-3,"mm")&TEXT(NOW()-3,"dd")&"!d2")<"replenish!",3,IF(INDIRECT(TEXT(NO W()-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(NO W()-2,"yyyy")&TEXT(NOW()-2,"mm")&TEXT(NOW()-2,"dd")&"!g10")<"replenish",2,IF(INDIRECT(TEXT(NO W()-3,"yyyy")&TEXT(NOW()-3,"mm")&TEXT(NOW()-3,"dd")&"!g10")<"replenish",2,IF(INDIRECT(TEXT(NO W()-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" wrote in message ... 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(E3F3,"REPLENISH!","No Action"). Each morning, I run a report to see what parts have been used, which becomes a new sheet in the workbook. Now, I want to add a formula that, whenever it sees "REPLENISH!," it will back through the workbook to count whether that same part needed replenishment on consecutive previous days. If it has, then the latest worksheet will report the number of days that part that part has been in need of replenishment. Can you help me? -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=399574 -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=399574 |
Bryan,
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(NO W()-2,"yyyy")&TEXT(NOW()-2,"mm")&TEXT(NOW()-2,"dd")&"!d2")<"replenish!",2,IF(INDIRECT(TEXT(NO W()-3,"yyyy")&TEXT(NOW()-3,"mm")&TEXT(NOW()-3,"dd")&"!d2")<"replenish!",3,IF(INDIRECT(TEXT(NO W()-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(NO W()-2,"yyyy")&TEXT(NOW()-2,"mm")&TEXT(NOW()-2,"dd")&"!g10")<"replenish",2,IF(INDIRECT(TEXT(NO W()-3,"yyyy")&TEXT(NOW()-3,"mm")&TEXT(NOW()-3,"dd")&"!g10")<"replenish",2,IF(INDIRECT(TEXT(NO W()-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" wrote in message ... 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(E3F3,"REPLENISH!","No Action"). Each morning, I run a report to see what parts have been used, which becomes a new sheet in the workbook. Now, I want to add a formula that, whenever it sees "REPLENISH!," it will back through the workbook to count whether that same part needed replenishment on consecutive previous days. If it has, then the latest worksheet will report the number of days that part that part has been in need of replenishment. Can you help me? -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=399574 -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=399574 |
wow!Thats what i call a formula!
I had in mind some helper columns,ie a 0 or 1 is diplayed depending on the no action/replenish result,and then i was going to sum over the sheets.In another set of cells would be the dates for today and previous two or three days and Indirect references to the sheets.I didnt get as far to figure out how to stop at the first no action (or 0) ... good work Bryan -- paul remove nospam for email addy! "Bryan Hessey" wrote: Hi, It's posibly that I expected a sheet for each day, but this may not (yet) be the case. Your next sheet should be named 20050827 and after that 20050826 for you to get more than 1 If your sheets are not that, then you need specify a date and use that reference in the formula, thus if I enter a date (say Friday's date, because I don't work Saturday nor Sunday) in cell J5 I would use the formula: =IF(D2<"replenish!,""",IF(INDIRECT(TEXT(J5-1,"yyyy")&TEXT(J5-1,"mm")&TEXT(J5-1,"dd")&"!d2")<"replenish!",1,IF(INDIRECT(TEXT( J5-2,"yyyy")&TEXT(J5-2,"mm")&TEXT(J5-2,"dd")&"!d2")<"replenish!",2,IF(INDIRECT(TEXT( J5-3,"yyyy")&TEXT(J5-3,"mm")&TEXT(J5-3,"dd")&"!d2")<"replenish!",3,IF(INDIRECT(TEXT( J5-4,"yyyy")&TEXT(J5-4,"mm")&TEXT(J5-4,"dd")&"!d2")<"replenish!",4,5))))) If this doesn't work you can email me the book at after you remove remove.remove blackgold21 Wrote: Bryan, Hey. I'm still getting 1's, I'm sorry to say. (Looks like you've done so much work to help.) I just wonder if my poor explanation is not the cause of the trouble. Is there any way I can post the workbook? "Bryan Hessey" wrote: 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(NO W()-2,"yyyy")&TEXT(NOW()-2,"mm")&TEXT(NOW()-2,"dd")&"!d2")<"replenish!",2,IF(INDIRECT(TEXT(NO W()-3,"yyyy")&TEXT(NOW()-3,"mm")&TEXT(NOW()-3,"dd")&"!d2")<"replenish!",3,IF(INDIRECT(TEXT(NO W()-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(NO W()-2,"yyyy")&TEXT(NOW()-2,"mm")&TEXT(NOW()-2,"dd")&"!g10")<"replenish",2,IF(INDIRECT(TEXT(NO W()-3,"yyyy")&TEXT(NOW()-3,"mm")&TEXT(NOW()-3,"dd")&"!g10")<"replenish",2,IF(INDIRECT(TEXT(NO W()-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" wrote in message ... 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(E3F3,"REPLENISH!","No Action"). Each morning, I run a report to see what parts have been used, which becomes a new sheet in the workbook. Now, I want to add a formula that, whenever it sees "REPLENISH!," it will back through the workbook to count whether that same part needed replenishment on consecutive previous days. If it has, then the latest worksheet will report the number of days that part that part has been in need of replenishment. Can you help me? -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=399574 -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=399574 -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=399574 |
Paul, I may yet have to resort to 'helper' cells, as on reflection I think that there will be sheets for Monday to Friday (except public holidays), and the only way that the sheetname will be detectable is by noting the last 5 (or so) sheet dates which can then be utilised in the formula. Perhaps a button can do the Sheetcopy and date fix. A second column would also be needed to go more than 5 days, due to the limit of '7 nested' that Excel has, however, I think a score of 5 would be sufficient for the OP's needs. The formula may also need to be adjusted to show how many of the last 5 days, rather than the currently selected 'consecutive-working-backwards' approach, but this will depend on requirements, and 'consecutive' looks good for their needs, as it would be on a Parts Supply system. Awaiting another OP response . . . . paul Wrote:[color=blue] wow!Thats what i call a formula! I had in mind some helper columns,ie a 0 or 1 is diplayed depending on the no action/replenish result,and then i was going to sum over the sheets.In another set of cells would be the dates for today and previous two or three days and Indirect references to the sheets.I didnt get as far to figure out how to stop at the first no action (or 0) ... good work Bryan -- paul remove nospam for email addy! "Bryan Hessey" wrote: Hi, It's posibly that I expected a sheet for each day, but this may not (yet) be the case. Your next sheet should be named 20050827 and after that 20050826 for you to get more than 1 If your sheets are not that, then you need specify a date and use that reference in the formula, thus if I enter a date (say Friday's date, because I don't work Saturday nor Sunday) in cell J5 I would use the formula: =IF(D2<"replenish!,""",IF(INDIRECT(TEXT(J5-1,"yyyy")&TEXT(J5-1,"mm")&TEXT(J5-1,"dd")&"!d2")<"replenish!",1,IF(INDIRECT(TEXT( J5-2,"yyyy")&TEXT(J5-2,"mm")&TEXT(J5-2,"dd")&"!d2")<"replenish!",2,IF(INDIRECT(TEXT( J5-3,"yyyy")&TEXT(J5-3,"mm")&TEXT(J5-3,"dd")&"!d2")<"replenish!",3,IF(INDIRECT(TEXT( J5-4,"yyyy")&TEXT(J5-4,"mm")&TEXT(J5-4,"dd")&"!d2")<"replenish!",4,5))))) If this doesn't work you can email me the book at after you remove remove.remove blackgold21 Wrote: Bryan, Hey. I'm still getting 1's, I'm sorry to say. (Looks like you've done so much work to help.) I just wonder if my poor explanation is not the cause of the trouble. Is there any way I can post the workbook? "Bryan Hessey" wrote: ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=399574 -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=399574 |
yes my first attempt was a series of lookups and the results were
concatenated so in a column to the right i had replenishreplenishnoactionreplenish,the weekends are another wrinkle.Praps some code mght be easier! -- paul remove nospam for email addy! "Bryan Hessey" wrote: [color=blue] Paul, I may yet have to resort to 'helper' cells, as on reflection I think that there will be sheets for Monday to Friday (except public holidays), and the only way that the sheetname will be detectable is by noting the last 5 (or so) sheet dates which can then be utilised in the formula. Perhaps a button can do the Sheetcopy and date fix. A second column would also be needed to go more than 5 days, due to the limit of '7 nested' that Excel has, however, I think a score of 5 would be sufficient for the OP's needs. The formula may also need to be adjusted to show how many of the last 5 days, rather than the currently selected 'consecutive-working-backwards' approach, but this will depend on requirements, and 'consecutive' looks good for their needs, as it would be on a Parts Supply system. Awaiting another OP response . . . . paul Wrote: wow!Thats what i call a formula! I had in mind some helper columns,ie a 0 or 1 is diplayed depending on the no action/replenish result,and then i was going to sum over the sheets.In another set of cells would be the dates for today and previous two or three days and Indirect references to the sheets.I didnt get as far to figure out how to stop at the first no action (or 0) ... good work Bryan -- paul remove nospam for email addy! "Bryan Hessey" wrote: Hi, It's posibly that I expected a sheet for each day, but this may not (yet) be the case. Your next sheet should be named 20050827 and after that 20050826 for you to get more than 1 If your sheets are not that, then you need specify a date and use that reference in the formula, thus if I enter a date (say Friday's date, because I don't work Saturday nor Sunday) in cell J5 I would use the formula: =IF(D2<"replenish!,""",IF(INDIRECT(TEXT(J5-1,"yyyy")&TEXT(J5-1,"mm")&TEXT(J5-1,"dd")&"!d2")<"replenish!",1,IF(INDIRECT(TEXT( J5-2,"yyyy")&TEXT(J5-2,"mm")&TEXT(J5-2,"dd")&"!d2")<"replenish!",2,IF(INDIRECT(TEXT( J5-3,"yyyy")&TEXT(J5-3,"mm")&TEXT(J5-3,"dd")&"!d2")<"replenish!",3,IF(INDIRECT(TEXT( J5-4,"yyyy")&TEXT(J5-4,"mm")&TEXT(J5-4,"dd")&"!d2")<"replenish!",4,5))))) If this doesn't work you can email me the book at after you remove remove.remove blackgold21 Wrote: Bryan, Hey. I'm still getting 1's, I'm sorry to say. (Looks like you've done so much work to help.) I just wonder if my poor explanation is not the cause of the trouble. Is there any way I can post the workbook? "Bryan Hessey" wrote: ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=399574 -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=399574 |
my date ref cell and my target cell for replenish and noaction are slightly
different but this worked for me =IF(F2<"replenish","",IF(INDIRECT(TEXT(J2-1,"yyyy")&TEXT(J2-1,"mm")&TEXT(J2-1,"dd")&"!F2")<"replenish",1,IF(INDIRECT(TEXT(J 2-2,"yyyy")&TEXT(J2-2,"mm")&TEXT(J2-2,"dd")&"!F2")<"replenish",2,IF(INDIRECT(TEXT(J 2-3,"yyyy")&TEXT(J2-3,"mm")&TEXT(J2-3,"dd")&"!F2")<"replenish",3,IF(INDIRECT(TEXT(J 2-4,"yyyy")&TEXT(J2-4,"mm")&TEXT(J2-4,"dd")&"!F2")<"replenish",4,"MORE"))))) you had a slight typing error at the first "replenish!,""" Bryan,.....I also dont use the ! -- paul remove nospam for email addy! "paul" wrote: [color=blue] yes my first attempt was a series of lookups and the results were concatenated so in a column to the right i had replenishreplenishnoactionreplenish,the weekends are another wrinkle.Praps some code mght be easier! -- paul remove nospam for email addy! "Bryan Hessey" wrote: Paul, I may yet have to resort to 'helper' cells, as on reflection I think that there will be sheets for Monday to Friday (except public holidays), and the only way that the sheetname will be detectable is by noting the last 5 (or so) sheet dates which can then be utilised in the formula. Perhaps a button can do the Sheetcopy and date fix. A second column would also be needed to go more than 5 days, due to the limit of '7 nested' that Excel has, however, I think a score of 5 would be sufficient for the OP's needs. The formula may also need to be adjusted to show how many of the last 5 days, rather than the currently selected 'consecutive-working-backwards' approach, but this will depend on requirements, and 'consecutive' looks good for their needs, as it would be on a Parts Supply system. Awaiting another OP response . . . . paul Wrote: wow!Thats what i call a formula! I had in mind some helper columns,ie a 0 or 1 is diplayed depending on the no action/replenish result,and then i was going to sum over the sheets.In another set of cells would be the dates for today and previous two or three days and Indirect references to the sheets.I didnt get as far to figure out how to stop at the first no action (or 0) ... good work Bryan -- paul remove nospam for email addy! "Bryan Hessey" wrote: Hi, It's posibly that I expected a sheet for each day, but this may not (yet) be the case. Your next sheet should be named 20050827 and after that 20050826 for you to get more than 1 If your sheets are not that, then you need specify a date and use that reference in the formula, thus if I enter a date (say Friday's date, because I don't work Saturday nor Sunday) in cell J5 I would use the formula: =IF(D2<"replenish!,""",IF(INDIRECT(TEXT(J5-1,"yyyy")&TEXT(J5-1,"mm")&TEXT(J5-1,"dd")&"!d2")<"replenish!",1,IF(INDIRECT(TEXT( J5-2,"yyyy")&TEXT(J5-2,"mm")&TEXT(J5-2,"dd")&"!d2")<"replenish!",2,IF(INDIRECT(TEXT( J5-3,"yyyy")&TEXT(J5-3,"mm")&TEXT(J5-3,"dd")&"!d2")<"replenish!",3,IF(INDIRECT(TEXT( J5-4,"yyyy")&TEXT(J5-4,"mm")&TEXT(J5-4,"dd")&"!d2")<"replenish!",4,5))))) If this doesn't work you can email me the book at after you remove remove.remove blackgold21 Wrote: Bryan, Hey. I'm still getting 1's, I'm sorry to say. (Looks like you've done so much work to help.) I just wonder if my poor explanation is not the cause of the trouble. Is there any way I can post the workbook? "Bryan Hessey" wrote: ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=399574 -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=399574 |
Paul, I did detect that error, with the misplaced ," and corrected it, also, because of the Saturday, Sunday and holidays situation decided to use 4 cells, G1 to G4 to hold the date of the sheets last saved (in reverse order, newest in G1), thus in the OP's sheet the formula: =IF(D2<"replenish!","",IF(INDIRECT(TEXT(G$1,"yyyy ")&TEXT(G$1,"mm")&TEXT(G$1,"dd")&"!D"&(ROW()))<"r eplenish!",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(INDIRE CT(TEXT(G$4,"yyyy")&TEXT(G$4,"mm")&TEXT(G$4,"dd")& "!d"&(ROW()))<"replenish!",4,5))))) was used, and the various numbers seemed to fair well for a good result for the OP. The word used was 'REPLENISH!' and the sheet was returned by email about 5 hours ago - hopefully all will be well. Cheers paul Wrote:[color=blue] my date ref cell and my target cell for replenish and noaction are slightly different but this worked for me =IF(F2<"replenish","",IF(INDIRECT(TEXT(J2-1,"yyyy")&TEXT(J2-1,"mm")&TEXT(J2-1,"dd")&"!F2")<"replenish",1,IF(INDIRECT(TEXT(J 2-2,"yyyy")&TEXT(J2-2,"mm")&TEXT(J2-2,"dd")&"!F2")<"replenish",2,IF(INDIRECT(TEXT(J 2-3,"yyyy")&TEXT(J2-3,"mm")&TEXT(J2-3,"dd")&"!F2")<"replenish",3,IF(INDIRECT(TEXT(J 2-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 after you remove remove.remove blackgold21 Wrote: Bryan, Hey. I'm still getting 1's, I'm sorry to say. (Looks like you've done so much work to help.) I just wonder if my poor explanation is not the cause of the trouble. Is there any way I can post the workbook? "Bryan Hessey" wrote: ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=399574 -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=399574 -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=399574 |
| All times are GMT +1. The time now is 10:16 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com