Obtaining a list from a schedule, dictated by date and shift

**UncleBear** · 07-12-2013, 03:21 AM

Hi everyone. This is a great forum.
I'm a new member and I have been teaching myself excel by, developing a production schedule, and consulting this forum, so far I've been going quite well.
I have run into a brick wall in obtaining a list of required production, on a particular day and shift, from an infinite schedule.

I have aattached a similar example to give you all an idea of what I mean. Basically I want to change the date and shift on sheet 2, and have a list of parts and amount required, according to the schedule on sheet 1.( I hope this all makes sense!)

Please help??

Uncle Bear

**FDibbins** · 07-12-2013, 11:52 AM

Hi and welcome to the forum

Try this in sheet2....
in E6, copied down...
=IF(Sheet1!A4="","",Sheet1!A4)
and then in F6, copied down...
=INDEX(Sheet1!$A$1:$AW$31,MATCH(Sheet2!E6,Sheet1!A:A,0),MATCH($F$3,Sheet1!$1:$1,0)+IF($F$4="AM",0,2))

**UncleBear** · 07-12-2013, 06:32 PM

That works great thanks.

How would I now get it to only display parts required, ie. eliminate blank cells?

Thanks again.

**FDibbins** · 07-12-2013, 10:39 PM

Sorry, I thought I already replied to this

=if(E6="","",INDEX(Sheet1!$A$1:$AW$31,MATCH(Sheet2!E6,Sheet1!A:A,0),MATCH($F$3,Sheet1!$1:$1,0)+IF($F$4="AM",0,2)))

**UncleBear** · 07-13-2013, 05:15 AM

I'm sorry, I think I missed something.

Your first reply worked nicely, than I was asking if there was a way to only list the parts required, without all of the parts with a lot of 0's next to them. You have replied with a very similar formula the second time around.

Please forgive me if I've missed something here.