Extension to a Index formula

**Icehockey44** · 01-21-2013, 12:17 PM

Hi All,

I am creating an automated scheduler for engineering works. I have the basics done, now just need to fine tune, but need some help?

I have the following formula:

IFERROR(INDEX('Region 1 Raw Data'!$J$1:$K$236,SMALL(IF(('Region 1 Raw Data'!$J$1:$J$236='Region 1'!A5)*('Region 1 Raw Data'!$N$1:$N$236<>"MINI"),ROW('Region 1 Raw Data'!$J$1:$J$236)),ROW($1:$1)),2),"")

Which basically says to look at a long list of sites that require some work, this list is sorted by engineer and the week that the previous work was varried out. Then it asks to look at it all again, and bring in the 2nd site info, and so on.

Well i need to add to this now, the following condition:

If the job has a "C" in the contract column then schedule the job in, if it blank " " then do not schedule the job in.

I hope this makes sense.

I look forward to your help on this one.

cheers

donna

**Icehockey44** · 01-23-2013, 08:01 AM

I have now added an example for you to play with.

cheers

Donna

**Icehockey44** · 01-28-2013, 07:50 AM

Hi there,

Is anyone able to help on this one

Cheers

Donna

**Fotis1991** · 01-28-2013, 08:03 AM

You don't say to us the expected results.

Does this works?

=INDEX('Region 2 Raw Data'!$L$2:$L$10;SMALL(IF('Region 2 Raw Data'!$K$2:$K$10&'Region 2 Raw Data'!$O$2:$O$10=$A2&"MINI";ROW('Region 2 Raw Data'!$L$2:$L$10)-1);ROW('Region 2 Raw Data'!K1)))

**Icehockey44** · 01-28-2013, 08:38 AM

Hi Fotis,

thanks for the reply

This only seems to bring back a #NUM error.

From looking at it, it doesn't look at column R as I only want on the planning sheet those that are NOT MINI (in column O), but does have a C (in column R)

I hope this helps,

Cheers

Donna

**Fotis1991** · 01-28-2013, 08:57 AM

You get #num error as you don't replace the semi colons to comma? Any way, as i have no expected result see the example that do this..

...From looking at it, it doesn't look at column R as I only want on the planning sheet those that are NOT MINI (in column O), but does have a C (in column R)