Fill cells with horizontal and vertical range criteria??

**kreesh** · 08-24-2017, 09:50 AM

Hi everyone

I have an attendance sheet(around 200 empls) in table 01 format and i need the same in table 02 format. Tried sumproduct, sumifs and getting errors.

Excel 2007 32 bit

	E	F	G	H
11		24-Aug-17	25-Aug-17	26-Aug-17
12	JAMES	P	L	P
13	PETER	L	P	L
14	STEVE	P	P	P

Sheet: Table:01(current)

Excel 2007 32 bit

	J	K	L
17	24-Aug-17	JAMES	P
18	24-Aug-17	PETER	L
19	24-Aug-17	STEVE	P
20	25-Aug-17	JAMES	L
21	25-Aug-17	PETER	P
22	25-Aug-17	STEVE	P
23	26-Aug-17	JAMES	P
24	26-Aug-17	PETER	L
25	26-Aug-17	STEVE	P

Sheet: Table 2(Required format)

**Glenn Kennedy** · 08-24-2017, 10:03 AM

J17:
=OFFSET($F$11,,(INT(ROWS($J$17:J17)-1)/3),,)

K17:
=OFFSET($E$12,MOD(ROWS($K$17:K17)-1,3),,,)

L17:
=OFFSET($F$12,(INT(ROWS($L$17:L17)-1)/3),MOD(ROWS($L$17:L17)-1,3),,)

Next time, please attach a sheet, rather than an editable image. It's quicker to work with a sheet, rather than having to fiddle around getting data into the right cells.