R
redstang423
Hi all, I have a strange problem that I cannot figure how how to fix o
even why it is occurring. I have a spreadsheet that is used to trac
efficiency and downtime during jobs. All the data is brought into tw
seperate sheets called Cumulative Efficiency and Cumulative Downtime
There is a third and final worksheet that is an analysis. The idea i
that I can plug in the dates for the analysis and it will automaticall
pull in all the job entries for all the dates included in the tim
period.
The first row of cells uses the VLOOKUP function to bring in the firs
job on the first date. I have a side calculation that calculates th
number of jobs. I have two cells that calculate the location of th
first and last job in the long list (ex, starts on 7/12 in row 35, end
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
reference to the date cell on the cumulative sheet and B54 is the dat
to which it is being compared. The plus 2 is simply a correction facto
for where the cells start. This ends up returning me the first cell an
last cell that I am interested in the analysis. This is where th
problem lies, but I cannot figure out why. I have data entered startin
6/13/2006. The sheet works perfectly until I hit 8/9/2006. At thi
point, the ending row is short by two, meaning if the data in th
Cumulative Efficiency sheet should end at cell 152, it only display
until cell 150. The same thing happens for the Cumulative Downtim
since it uses the same equations, just different cell references. Th
starting date does not effect this, meaning if I enter test data unti
8/18 and have the analysis look up data from 8/10-8/18, it is still of
by two and not four. I tried changing the conditional to <(B54+1) t
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. T
hopefully clarify my situation further, what I am currentl
experiencing is that for 8/9, I have 4 jobs entered, but only 2 wil
show up. The number of jobs per day is different every day, and if
test jobs to the 9th to bring the total up to 8, it will only displa
6th. Again this only occurs when the analysis runs including and pas
August 9th.
I appreciate any help anyone can give me, and let me know how I ca
make myself more clear if need be
even why it is occurring. I have a spreadsheet that is used to trac
efficiency and downtime during jobs. All the data is brought into tw
seperate sheets called Cumulative Efficiency and Cumulative Downtime
There is a third and final worksheet that is an analysis. The idea i
that I can plug in the dates for the analysis and it will automaticall
pull in all the job entries for all the dates included in the tim
period.
The first row of cells uses the VLOOKUP function to bring in the firs
job on the first date. I have a side calculation that calculates th
number of jobs. I have two cells that calculate the location of th
first and last job in the long list (ex, starts on 7/12 in row 35, end
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
reference to the date cell on the cumulative sheet and B54 is the dat
to which it is being compared. The plus 2 is simply a correction facto
for where the cells start. This ends up returning me the first cell an
last cell that I am interested in the analysis. This is where th
problem lies, but I cannot figure out why. I have data entered startin
6/13/2006. The sheet works perfectly until I hit 8/9/2006. At thi
point, the ending row is short by two, meaning if the data in th
Cumulative Efficiency sheet should end at cell 152, it only display
until cell 150. The same thing happens for the Cumulative Downtim
since it uses the same equations, just different cell references. Th
starting date does not effect this, meaning if I enter test data unti
8/18 and have the analysis look up data from 8/10-8/18, it is still of
by two and not four. I tried changing the conditional to <(B54+1) t
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. T
hopefully clarify my situation further, what I am currentl
experiencing is that for 8/9, I have 4 jobs entered, but only 2 wil
show up. The number of jobs per day is different every day, and if
test jobs to the 9th to bring the total up to 8, it will only displa
6th. Again this only occurs when the analysis runs including and pas
August 9th.
I appreciate any help anyone can give me, and let me know how I ca
make myself more clear if need be