Skip Blank Cells

I was trying to figure this out on my own from other posts as it seems that it is a common question. Sadly I was not able to...

I have three shifts for volunteers (1,2,3) which are assgined on the "full contact info" tab. I would like the Shift Schedule tab to populate only those that meet the criteria and not have blank cells. This is the formula I am working with now:

Please Login or Register  to view this content.

Try this in B6 within the Shift Schedule worksheet and drag across/down:

Please Login or Register  to view this content.

Sorry, I believe I may have interpreted your initial question incorrectly. If what you wish is to have the different shift schedules entered sequentially, you can try this formula entered as an array formula with CTRL+SHIFT+ENTER in B6:

Please Login or Register  to view this content.

I don't believe the code that you are proposing will work as you currently have it written.

As far as my code goes, I am assuming by "Shift 1, Shift 2, Shift 3" you are referring to the 8am to 11am, 10am to 1pm, and 12pm to 3pm shifts respectively. If this is correct, dragging over/down the formula should pickup the change in rows as well as shifts automatically.

Try this...

Data Range

	A	B	C	D	E
4	Shift Schedule
5	----------------	8 am to 11 am	10 am to 1 pm	12pm to 3pm	----------------
6		1	2	3
7	1	Jane smith	Joe Smith
8	2	Joe doe
9	3
10	4
11	5

Add a row to show the actual shift number.

This array formula** entered in B7:

=IFERROR(INDEX('Full contact info'!$A:$A,SMALL(IF('Full contact info'!$H$2:$H$4=B$6,ROW('Full contact info'!H$2:H$4)),ROWS(B$7:B7))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy across to D7 then down as needed.

Oh, that should be an easy fix. Just make sure that your array formula ranges are consistent, and that should be all. That is, if you want to include row thirty (within your Full Contact Info worksheet), your formula would look like this:

Please Login or Register  to view this content.

Note that it would be better to fill through more rows than you would expect. This means that if your max number of volunteers would be around somewhere of 250, then change the range to go through row 300, and the IFERROR will take care of the value error.

That's because in your sample file there are only 3 names entered on the Full Contact Info sheet.

If your real file has more data then just extend the highlighted ranges to suit:

=IFERROR(INDEX('Full contact info'!$A:$A,SMALL(IF('Full contact info'!$H$2:$H$4=B$6,ROW('Full contact info'!H$2:H$4)),ROWS(B$7:B7))),"")

One more question, I was wondering why CTRL+ SHIFT+ ENTER is required when entering this formulas?

Because it's an array formula.

See this...

http://www.cpearson.com/Excel/ArrayFormulas.aspx