Hi all, I have a strange problem that I cannot figure how how to fix or
even why it is occurring. I have a spreadsheet that is used to track
efficiency and downtime during jobs. All the data is brought into two
seperate sheets called Cumulative Efficiency and Cumulative Downtime.
There is a third and final worksheet that is an analysis. The idea is
that I can plug in the dates for the analysis and it will automatically
pull in all the job entries for all the dates included in the time
period.

The first row of cells uses the VLOOKUP function to bring in the first
job on the first date. I have a side calculation that calculates the
number of jobs. I have two cells that calculate the location of the
first and last job in the long list (ex, starts on 7/12 in row 35, ends
on 7/26 in row 127) by using an array sum looking like this...
{=SUM(IF((INDIRECT(E54)<=(B54),1,0))+2)} where E54 is the is just a
reference to the date cell on the cumulative sheet and B54 is the date
to which it is being compared. The plus 2 is simply a correction factor
for where the cells start. This ends up returning me the first cell and
last cell that I am interested in the analysis. This is where the
problem lies, but I cannot figure out why. I have data entered starting
6/13/2006. The sheet works perfectly until I hit 8/9/2006. At this
point, the ending row is short by two, meaning if the data in the
Cumulative Efficiency sheet should end at cell 152, it only displays
until cell 150. The same thing happens for the Cumulative Downtime
since it uses the same equations, just different cell references. The
starting date does not effect this, meaning if I enter test data until
8/18 and have the analysis look up data from 8/10-8/18, it is still off
by two and not four. I tried changing the conditional to <(B54+1) to
tell it to look for dates before the next day (ex, If 8/9 were entered,
it would look for all dates BEFORE 8/10.) but it works the same. To
hopefully clarify my situation further, what I am currently
experiencing is that for 8/9, I have 4 jobs entered, but only 2 will
show up. The number of jobs per day is different every day, and if I
test jobs to the 9th to bring the total up to 8, it will only display
6th. Again this only occurs when the analysis runs including and past
August 9th.

I appreciate any help anyone can give me, and let me know how I can
make myself more clear if need be!


--
redstang423
------------------------------------------------------------------------
redstang423's Profile: http://www.excelforum.com/member.php...o&userid=37333
View this thread: http://www.excelforum.com/showthread...hreadid=570308

Additionally, I realize I could simply add an IF statement checking the
date and adding two if nessecary, but I'd prefer to solve it a more
proper way.


--
redstang423
------------------------------------------------------------------------
redstang423's Profile: http://www.excelforum.com/member.php...o&userid=37333
View this thread: http://www.excelforum.com/showthread...hreadid=570308