Formula for Translating Grid to a Table

[gmmark1125]

Hello,

I use a gantt-like grid I created in Excel to schedule our production team. What I'm trying to do is to lookup the job that is listed on the grid and populate the table with the employee name and date so that I can print off a schedule for each employee. The left column has the name of the employee followed by a number so that there can be multiple jobs scheduled on the same day. The columns are all dates. The Here is what the grid looks like:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]10/23[/TD]
[TD]10/24[/TD]
[TD]10/25[/TD]
[TD]10/26[/TD]
[TD]10/27[/TD]
[/TR]
[TR]
[TD]Kelly1[/TD]
[TD]Job1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Kelly2[/TD]
[TD]Job2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Kelly3[/TD]
[TD][/TD]
[TD]Job3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Kelly4[/TD]
[TD][/TD]
[TD]Job4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Kelly5[/TD]
[TD][/TD]
[TD][/TD]
[TD]Job5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Here is the table I am trying to auto-populate by looking up the job name:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Order[/TD]
[TD]Model[/TD]
[TD]Qty[/TD]
[TD]Employee[/TD]
[TD]Job[/TD]
[TD]Start Date[/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD]A[/TD]
[TD]1[/TD]
[TD]POPULATE[/TD]
[TD]Job1[/TD]
[TD]POPULATE[/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD]A[/TD]
[TD]1[/TD]
[TD]POPULATE[/TD]
[TD]Job2[/TD]
[TD]POPULATE[/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD]A[/TD]
[TD]1[/TD]
[TD]POPULATE[/TD]
[TD]Job3[/TD]
[TD]POPULATE[/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD]A[/TD]
[TD]1[/TD]
[TD]POPULATE[/TD]
[TD]Job4[/TD]
[TD]POPULATE[/TD]
[/TR]
</tbody>[/TABLE]


Thank you in advance! I appreciate your Excel skills

 

[Snakehips]

gmmark1125,
Maybe something like....

Excel 2010
	A	B	C	D	E	F
1		Oct-23	Oct-24	Oct-25	Oct-26	Oct-27
2	Kelly1	Job1
3	Kelly2	Job2
4	Kelly3		Job3
5	Kelly4		Job4
6	Kelly5			Job5
Sheet4

Excel 2010
	A	B	C	D	E	F
1	Order	Model	Qty	Employee	Job	Start Date
2	101	A	1	Kelly1	Job1	Oct-23
3	101	A	1	Kelly2	Job2	Oct-23
4	101	A	1	Kelly3	Job3	Oct-24
5	101	A	1	Kelly4	Job4	Oct-24
Sheet5

Cell Formulas
Range	Formula
D2	=IFERROR(INDEX(Sheet4!$A$1:$A$20,SUMPRODUCT((Sheet4!$B$2:$X$20=E2)*ROW(Sheet4!$B$2:$X$20)),1),"")
F2	=IFERROR(INDEX(Sheet4!$A$1:$X$1,1,SUMPRODUCT((Sheet4!$B$2:$X$20=E2)*COLUMN(Sheet4!$B$2:$X$20))),"")

Hope that helps.

 

[gmmark1125]

gmmark1125,
Maybe something like....

Excel 2010

	A	B	C	D	E	F
Kelly1	Job1
Kelly2	Job2
Kelly3	Job3
Kelly4	Job4
Kelly5	Job5

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Oct-23[/TD]
[TD="align: right"]Oct-24[/TD]
[TD="align: right"]Oct-25[/TD]
[TD="align: right"]Oct-26[/TD]
[TD="align: right"]Oct-27[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet4​

Excel 2010

	A	B	C	D	E	F
Order	Model	Qty	Employee	Job	Start Date
A	Kelly1	Job1
A	Kelly2	Job2
A	Kelly3	Job3
A	Kelly4	Job4

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]101[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]Oct-23[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]101[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]Oct-23[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]101[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]Oct-24[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]101[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]Oct-24[/TD]

</tbody>

Sheet5​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]=IFERROR(INDEX(Sheet4!$A$1:$A$20,SUMPRODUCT((Sheet4!$B$2:$X$20=E2)*ROW(Sheet4!$B$2:$X$20)),1),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=IFERROR(INDEX(Sheet4!$A$1:$X$1,1,SUMPRODUCT((Sheet4!$B$2:$X$20=E2)*COLUMN(Sheet4!$B$2:$X$20))),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Hope that helps.

This worked great! Thank you. The formulas are returning a "0" in the Employee column of sheet 5 and "0-Jan" in the Start Date column of sheet 5 instead of "". Any idea why that might be happening?

 

[Snakehips]

This worked great! Thank you. The formulas are returning a "0" in the Employee column of sheet 5 and "0-Jan" in the Start Date column of sheet 5 instead of "". Any idea why that might be happening?

Pleased that helped.

Zero in "" can often be down to vagary of formats.
Maybe try >> Options > Advanced > Display Options for this worksheet >. Un-check. ' Show a zero in cells that have 0 value.