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