autofill cells by entering date

[azii]

Hello there
I am trying to solve this issue but I am stuck. I need help to resolve this problem
I have data In table-1 and I have to enter date and get the data datewise in table-2, I have 3 heads in table-2 i.e. Fatima, Engro and Sona/ffc
table-2 is showing position.
I am using this formula
=IF($K$2=$D$2,INDEX($B$2:$I$50,3,3),IF($K$2=$E$2,INDEX($B$2:$I$50,3,4),IF($K$2=$F$2,INDEX($B$2:$I$50,3,5),IF($K$2=$G$2,INDEX($B$2:$I$50,3,6),IF($K$2=$H$2,INDEX($B$2:$I$50,3,7),IF($K$2=$D$2,INDEX($B$2:$I$50,3,8),""))))))
but I can't follow this formula because I would have more data spreading horizontally (e.g. A to Z and so on...)
please help me.


TABLE-1

	A	B	C	D	E	F	G	H
1	Receiving Date		16-Nov-12	26-Nov-12	27-Dec-12	28-Feb-13	5-Mar-13	22-Mar-13
2	Parameters	Unit	Fatima	Fatima	Fatima	Fatima	Fatima	Fatima
3	Moisture	%	0.18	0.11	0.14	0.25	0.22	0.21
4	Biuret	%	0.97	0.97	1.12	1.03	0.91	1
5	Mesh Size
6	+2.80 mm	%	2.5	6.5	2.1	2.0	5.7	3.8
7	+2.40 mm	%	16.4	17.5	14.0	13.6	17.7	20.4
8	+2.00 mm	%	46.7	39.4	46.7	45.8	40.2	44.1
9	+1.70 mm	%	25.3	31.1	29.0	24.3	24.1	24.5
10	+1.40 mm	%	5.6	4.5	5.2	8.0	7.0	5.0
11	+1.00 mm	%	2.5	0.9	1.5	4.3	2.5	1.4
12	+0.50 mm	%	0.8	0.1	0.8	1.7	1.9	0.7
13	-0.50 mm	%	0.2	0	0.7	0.3	0.9	0.1
14	1 ~ 2.4 mm	%	96.5	93.4	96.4	96.0	91.5	95.5
15	Fines	%	1.0	0.1	1.5	2.0	2.8	0.8
16	Avg. Prill Size	mm	2.11	2.17	2.09	2.05	2.11	2.16
17	Avg. Crush Strength	Grams	807	1291	806	806	802	817
18	Parameters	Unit	Engro	Engro	Engro	Engro	Engro	Engro
19	Moisture	%	0.21	0.14	0.22	0.32	0.24	0.27
20	Biuret	%	1.03	0.8	0.99	1.19	0.94	1.02
21	Mesh Size
22	+2.80 mm	%	5.5	79.1	4.6	0.9	6.3	5.8
23	+2.40 mm	%	14.6	14.7	13.3	9.0	17.3	17.1
24	+2.00 mm	%	34.2	4.7	34.2	28.4	36.1	32.3
25	+1.70 mm	%	35.0	4.4	36.4	50.3	29.6	37.1
26	+1.40 mm	%	8.4	0.3	9.4	6.0	8.5	6.0
27	+1.00 mm	%	1.6	0.1	1.6	2.6	1.5	1.0
28	+0.50 mm	%	0.5	0	0.4	1.8	0.5	0.6
29	-0.50 mm	%	0.2	0	0.1	1.0	0.2	0.1
30	1 ~ 2.4 mm	%	93.8	20.9	94.9	96.3	93.0	93.5
31	Fines	%	0.7	0	0.5	2.8	0.7	0.7
32	Avg. Prill Size	mm	2.09	2.94	2.07	1.95	2.13	2.1
33	Avg. Crush Strength	Grams	819	4645	804	801	806	812.0
34	Parameters	Unit	Sona/ffc	Sona/ffc	Sona/ffc	Sona/ffc	Sona/ffc	Sona/ffc
35	Moisture	%	0.10	0.19	0.16	0.22	0.29	0.24
36	Biuret	%	0.88	0.96	0.91	0.91	0.92	0.9
37	Mesh Size
38	+2.80 mm	%	5.7	3	3.9	7.8	5.3	3.3
39	+2.40 mm	%	15.9	14.5	10.3	18.7	17.9	10.6
40	+2.00 mm	%	31.9	60.8	70.0	52.0	41.1	62.8
41	+1.70 mm	%	34.7	18.8	13.1	13.0	25.4	20.2
42	+1.40 mm	%	9.6	1.9	1.6	4.9	7.2	1.8
43	+1.00 mm	%	1.5	0.9	0.7	2.4	2.1	0.8
44	+0.50 mm	%	0.5	0.1	0.3	1.1	0.9	0.4
45	-0.50 mm	%	0.2	0	0.1	0.1	0.1	0.1
46	1 ~ 2.4 mm	%	93.6	96.9	95.7	91.0	93.7	96.2
47	Fines	%	0.7	0.1	0.4	1.2	1.0	0.5
48	Avg. Prill Size	mm	2.09	2.18	2.19	2.21	2.14	2.16
49	Avg. Crush Strength	Grams	814	951	821	822	808	819

<colgroup><col><col><col><col span="2"><col><col><col><col></colgroup><tbody>
</tbody>

TABLE-2

5-Mar-13
Fatima	Engro	Sona/ffc
0.22	0.24	0.92
0.91	0.94	0.94
-	-	-
5.7	6.3	6.3
17.7	0.2	0.2
40.2	36.1	36.1
24.1	29.6	29.6
7	8.5	8.5
2.5	1.5	1.5
1.9	0.5	0.5
0.9	0.2	0.2
91.5	93	93
2.8	0.7	0.7
2.11	2.13	2.13
802	806	806

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[p45cal]

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Receiving Date		16-Nov-12	26-Nov-12	27-Dec-12	28-Feb-13	05-Mar-13	22-Mar-13			22-Mar-13
2	Parameters	Unit	Fatima	Fatima	Fatima	Fatima	Fatima	Fatima			Fatima	Engro	Sona/ffc
3	Moisture	%	0.18	0.11	0.14	0.25	0.22	0.21			0.21	0.27	0.24
4	Biuret	%	0.97	0.97	1.12	1.03	0.91	1			1	1.02	0.9
5	Mesh Size										0	0	0
6	+2.80 mm	%	2.5	6.5	2.1	2	5.7	3.8			3.8	5.8	3.3
7	+2.40 mm	%	16.4	17.5	14	13.6	17.7	20.4			20.4	17.1	10.6
8	+2.00 mm	%	46.7	39.4	46.7	45.8	40.2	44.1			44.1	32.3	62.8
9	+1.70 mm	%	25.3	31.1	29	24.3	24.1	24.5			24.5	37.1	20.2
10	+1.40 mm	%	5.6	4.5	5.2	8	7	5			5	6	1.8
11	+1.00 mm	%	2.5	0.9	1.5	4.3	2.5	1.4			1.4	1	0.8
12	+0.50 mm	%	0.8	0.1	0.8	1.7	1.9	0.7			0.7	0.6	0.4
13	-0.50 mm	%	0.2	0	0.7	0.3	0.9	0.1			0.1	0.1	0.1
14	1 ~ 2.4 mm	%	96.5	93.4	96.4	96	91.5	95.5			95.5	93.5	96.2
15	Fines	%	1	0.1	1.5	2	2.8	0.8			0.8	0.7	0.5
16	Avg. Prill Size	mm	2.11	2.17	2.09	2.05	2.11	2.16			2.16	2.1	2.16
17	Avg. Crush Strength	Grams	807	1291	806	806	802	817			817	812	819
18	Parameters	Unit	Engro	Engro	Engro	Engro	Engro	Engro
19	Moisture	%	0.21	0.14	0.22	0.32	0.24	0.27
20	Biuret	%	1.03	0.8	0.99	1.19	0.94	1.02
21	Mesh Size
22	+2.80 mm	%	5.5	79.1	4.6	0.9	6.3	5.8
Sheet

I've only shown the formulae for 3 cells. In fact the you should select first K2:K17, then copy the formula in K2, then enter it with Ctrl+Shift+Enter (not just Enter). This will enter the formula into all the cells selected as an Array Formula.
Do the same with the opther two columns.

 

[p45cal]

You don't actually needall those $ symbols in the formula, they can be:

Excel Workbook
	K	L	M
2	Fatima	Engro	Sona/ffc
Sheet

Of course, adjust C1:H1 to C1:Z1 if your data stretches to column Z (you could even go beyond your last column of data to cover for additional data).

 

[Andrew Poulsom]

With your first table in the range A1:A49, in K3 copied down and across:

=INDEX(INDEX($C$2:$H$49,0,MATCH($K$1,$C$1:$H$1,FALSE)),MATCH(K$2,$C$2:$C$49,FALSE)+ROWS(K$3:K3))

 

[azii]

Dear p45cal
thanks for helping but one problem i found
when I paste the formula =OFFSET($B$19:$B$34,0,MATCH($J$2,$C$2:$H$2)) then
it shows #VALUE!
what i am doing wrong.....

 

[p45cal]

Assuming that the date is in J2, the date headers are in C2:H2, then you're probably not Array-entering the formula; commit the formula to the sheet with Ctrl+Shift+Enter, not just Enter. The curly brackets should appear in the formula put there by Excel (they shouldn't be typed in).

 

[azii]

Ctrl+Shift+Enter is not working
I am using office-10
I took values of c2:h2 to c19:h19
=OFFSET($B$19:$B$34,0,MATCH($J$2,$C$19:$H$19)) then
it is not working even now.........

 

[Andrew Poulsom]

Did you try my formula? I doesn't require Ctrl+Shift+Enter.

 

[azii]

I have tried your formula =OFFSET(B2:B17,0,MATCH(K1,C1:H1)). I just change the exact cell ref. according to my sheet.
I couldn't understand why this is not working. see the data

		16-11-12	26-11-12	27-12-12	28-02-13	05-03-13	22-03-13
Parameters	Unit	Engro	Engro	Engro	Engro	Engro	Engro
Moisture	%	0.21	0.14	0.22	0.32	0.24	0.27
Biuret	%	1.03	0.8	0.99	1.19	0.94	1.02
Mesh Size
+2.80 mm	%	5.5	79.1	4.6	0.9	6.3	5.8
+2.40 mm	%	14.6	14.7	13.3	9.0	17.3	17.1
+2.00 mm	%	34.2	4.7	34.2	28.4	36.1	32.3
+1.70 mm	%	35.0	4.4	36.4	50.3	29.6	37.1
+1.40 mm	%	8.4	0.3	9.4	6.0	8.5	6.0
+1.00 mm	%	1.6	0.1	1.6	2.6	1.5	1.0
+0.50 mm	%	0.5	0	0.4	1.8	0.5	0.6
-0.50 mm	%	0.2	0	0.1	1.0	0.2	0.1
1 ~ 2.4 mm	%	93.8	20.9	94.9	96.3	93.0	93.5
Fines	%	0.7	0	0.5	2.8	0.7	0.7
Avg. Prill Size	mm	2.09	2.94	2.07	1.95	2.13	2.1
Avg. Crush Strength	Grams	819	4645	804	801	806	812.0

<colgroup><col><col><col span="2"><col><col><col><col></colgroup><tbody>
</tbody>

 

[p45cal]

try the file here