Array Formula works in one portion, but not after being altered and moved

[kspeese]

I figured out exactly what I wanted to do and got it to work in a test excel sheet. However, when integrating it into the actual workbook I wanted, I was unable to get it to work. So, I used the same cell references I need to work in my actual workbook and pasted it all back to the test excel sheet.

So, the test excel sheet has the working formulas up at the top, and a duplicate of what I need to work in the actual cells I need them to work in. Changing the shift start time should group any persons with the same shift togethor. I'm still fairly new, but I think the only portion that could have been changed is the portion that says 1:1.. I figure that is relative to the array so it shouldn't be changed, but not sure what else to do.

Here's an array formula that works:

Please Login or Register  to view this content.

Here's the array formula to be in the correct cells that doesn't work:

Please Login or Register  to view this content.

I figure this has to be easy for someone, but I spent like 2 hours trying to get it to work in the different cell area.

Thanks

[vlady]

look at the blue part that is missing so it would start in row

=....SMALL(IF($W$34:$W$37=$AA$37,ROW($W$34:$W$37)-ROW($W$33)+1),ROW(1:1)),2)),.....

[kspeese]

Thanks, that fixed it. Just curious, why would that extra portion of the formula need to be added? I just moved the referenced cells, but they were all relative to each other.

[vlady]

Welcome.Nice to hear that.

index value has to start from 1 or in your case the second so whenever you start from any row other than 1 , you need to subtract the first row ROW($W$33) then +1 to start in 2nd row of index