Advanced Excel Formula To Copy Cell Values

[DushiPunda]

So, it's a little hard for me to explain what I'm trying to do. At work, we have a public word document that I painstakingly converted into an Excel spreadsheet so that I could do some automation. One of the sections is a 6 day schedule (current day + 5 days) for a position that is manned by one person per day. That data comes from a separate (excel) schedule put out by that position. So I figured I could copy the worksheet from their schedule workbook to my new workbook, and automatically copy the information from it. Like I said, a little hard to explain, so let me make some tables for you to see.

---

Sheet1, the section I was talking about:

	A	B	C	D	E	F
1	01 Jan 17	02 Jan 17	03 Jan 17	04 Jan 17	05 Jan 17	06 Jan 17
2	Worker 1	Worker 2	Worker 2	Worker 3	Worker 3	Worker 4

<tbody>
</tbody>

A2: =INDEX(Sheet2!$B$3:$B$64,MATCH(A1,Sheet2!$A$3:$A$64,0))

---

Sheet2,

	A	B
2	Date	Name
3	01 Jan 17	Worker 1
4	02 Jan 17	Worker 2
5	03 Jan 17	Worker 2
6	04 Jan 17	Worker 3
7	05 Jan 17	Worker 3
8	06 Jan 17	Worker 4
9	07 Jan 17	Worker 5

<tbody>
</tbody>

So, I created a formula that allows me to find the very first worker, even if there are blank cells:

B3:
=IF(Sheet3!B4<>"",Sheet3!B4,IF(Sheet3!E4<>"",Sheet3!E4,IF(Sheet3!H4<>"",Sheet3!H4,IF(Sheet3!K4<>"",Sheet3!K4,IF(Sheet3!N4<>"",Sheet3!N4,IF(Sheet3!Q4<>"",Sheet3!Q4,Sheet3!T4))))))

The issue is, this is great for the 1st of the month, but I can't come up with a way to effectively do this for the entire month. If I could find a way to go from whatever cell the above formula copies from to the next cell (it would actually be 3 cells to the right due to the formatting, see below) and copy that data, then that would be ideal. But I can't think of a way to do that.

---

Sheet3,


This one is a little hard to create using the table function of the forums because there are some merged cells.

These are static and the range is merged:

A3:C3 = Sunday
D3:F3 = Monday
G3:I3 = Tuesday
J3:L3 = Wednesday
M3:O3 = Thursday
P3:R3 = Friday
S3:U3 = Saturday

Date cells (if applicable):

A4,D4,G4,J4,M4,P4,S4

So, if the 1st of the month isn't on Sunday, A4 (above) and B4:C4 (below) are going to be blank. For example, February 1st is on a Wednesday, so the following are blank:

A4, B4:C4, D4, E4:F4, G4, H4:I4

Worker Cells, range is merged:

B4:C4 = Worker 1
E4:F4 = Worker 2
H4:I4 = Worker 2
K4:L4 = Worker 3
N4:O4 = Worker 3
Q4:R4 = Worker 4
T4:U4 = Worker 5

Keep in mind, I've only given you the cells for the first week.

2nd Week starts on Row 16.
3rd Week on Row 27.
4th Week on Row 37.
5th week on Row 47.

---

So the cause of my issues is essentially the format of their schedules. I could reformat or manually handjam everything into the applicable cells on Sheet2, but I'm trying to avoid that, if possible. Does anyone have any suggestion? Thanks.

 

[DushiPunda]

So I guess for the first week, the easiest way would be to, essentially, create a list from A4:U4 excluding blanks and numbers (so we don't include the date cells in our list)...I've found a way to do this excluding the blanks, but I cannot seem to figure out how to also exclude numbers.

Excluding blanks:

{=IFERROR(INDEX(Sheet3!$A$4:$U$4,1,SMALL(IF(Sheet3!$A$4:$U$4<>"",COLUMN(Sheet3!$A$4:$U$4)-ROW($1:$1)+1),ROWS($1:1))),"")}

This formula returns (Note: these formulas were looking at February's schedule which starts on a Wednesday):

1
Worker1
2
Worker1
3
Worker2
4
Worker3

If I try to add an AND in there and include a check to see if it is a number, it doesn't work properly. Example:

{=IFERROR(INDEX(Sheet3!$A$4:$U$4,1,SMALL(IF(AND(Sheet3!$A$4:$U$4<>"",NOT(ISNUMBER(Sheet3!$A$4:$U$4))),COLUMN(Sheet3!$A$4:$U$4)-ROW($1:$1)+1),ROWS($1:1))),"")}

This returns:

0
<blank>-blank-
<blank>-blank-
<blank>-blank-
<blank>-blank-
<blank>-blank-
<blank>-blank-
<blank>

Just out of curiosity, I changed it to only exclude numbers like so:

{=IFERROR(INDEX(Sheet3!$A$4:$U$4,1,SMALL(IF(NOT(ISNUMBER(Sheet3!$A$4:$U$4)),COLUMN(Sheet3!$A$4:$U$4)-ROW($1:$1)+1),ROWS($1:1))),"")}

This returns:

0
0
0
0
0
0
0
0
0
Worker1
0
Worker1
0
Worker2
0
Worker3

And, again, just out of curiosity, I changed it to ONLY count numbers:

{=IFERROR(INDEX(Sheet3!$A$4:$U$4,1,SMALL(IF(ISNUMBER(Sheet3!$A$4:$U$4),COLUMN(Sheet3!$A$4:$U$4)-ROW($1:$1)+1),ROWS($1:1))),"")}

This returns:

1
2
3
4

So at this point, I'm not sure what the best course of action is.</blank></blank></blank></blank></blank></blank></blank>