autofill cells by entering date

azii

Board Regular
Joined
May 26, 2011
Messages
67
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

ABCDEFGH
1Receiving Date 16-Nov-1226-Nov-1227-Dec-1228-Feb-135-Mar-1322-Mar-13
2ParametersUnitFatimaFatima Fatima Fatima Fatima Fatima
3Moisture% 0.180.11 0.140.25 0.220.21
4Biuret% 0.970.97 1.121.03 0.911
5Mesh Size
6+2.80 mm% 2.56.5 2.1 2.0 5.73.8
7+2.40 mm% 16.417.5 14.0 13.6 17.720.4
8+2.00 mm% 46.739.4 46.7 45.8 40.244.1
9+1.70 mm% 25.331.1 29.0 24.3 24.124.5
10+1.40 mm% 5.64.5 5.2 8.0 7.0 5.0
11+1.00 mm% 2.50.9 1.5 4.3 2.51.4
12+0.50 mm% 0.80.1 0.8 1.7 1.90.7
13-0.50 mm% 0.20 0.7 0.3 0.90.1
141 ~ 2.4 mm% 96.593.4 96.4 96.0 91.595.5
15Fines% 1.00.1 1.5 2.0 2.80.8
16Avg. Prill Sizemm 2.112.17 2.09 2.05 2.112.16
17Avg. Crush StrengthGrams8071291 806806 802817
18ParametersUnitEngroEngro Engro Engro Engro Engro
19Moisture% 0.210.14 0.220.32 0.240.27
20Biuret% 1.030.8 0.991.19 0.941.02
21Mesh Size
22+2.80 mm% 5.579.1 4.6 0.9 6.3 5.8
23+2.40 mm% 14.614.7 13.3 9.0 17.3 17.1
24+2.00 mm% 34.24.7 34.2 28.4 36.1 32.3
25+1.70 mm% 35.04.4 36.4 50.3 29.6 37.1
26+1.40 mm% 8.40.3 9.4 6.0 8.5 6.0
27+1.00 mm% 1.60.1 1.6 2.6 1.5 1.0
28+0.50 mm% 0.50 0.4 1.8 0.5 0.6
29-0.50 mm% 0.20 0.1 1.0 0.2 0.1
301 ~ 2.4 mm% 93.820.9 94.9 96.3 93.0 93.5
31Fines% 0.70 0.5 2.8 0.7 0.7
32Avg. Prill Sizemm 2.092.94 2.07 1.95 2.13 2.1
33Avg. Crush StrengthGrams8194645 804801 806 812.0
34ParametersUnitSona/ffcSona/ffc Sona/ffc Sona/ffc Sona/ffc Sona/ffc
35Moisture% 0.100.19 0.160.22 0.290.24
36Biuret% 0.880.96 0.910.91 0.920.9
37Mesh Size
38+2.80 mm% 5.73 3.9 7.8 5.33.3
39+2.40 mm% 15.914.5 10.3 18.7 17.910.6
40+2.00 mm% 31.960.8 70.0 52.0 41.162.8
41+1.70 mm% 34.718.8 13.1 13.0 25.420.2
42+1.40 mm% 9.61.9 1.6 4.9 7.21.8
43+1.00 mm% 1.50.9 0.7 2.4 2.10.8
44+0.50 mm% 0.50.1 0.3 1.1 0.90.4
45-0.50 mm% 0.20 0.1 0.1 0.10.1
461 ~ 2.4 mm% 93.696.9 95.7 91.0 93.796.2
47Fines% 0.70.1 0.4 1.2 1.00.5
48Avg. Prill Sizemm 2.092.18 2.19 2.21 2.142.16
49Avg. Crush StrengthGrams814951 821822 808819




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


TABLE-2

5-Mar-13
FatimaEngroSona/ffc
0.220.240.92
0.910.940.94
- - -
5.76.36.3
17.70.20.2
40.236.136.1
24.129.629.6
78.58.5
2.51.51.5
1.90.50.5
0.90.20.2
91.59393
2.80.70.7
2.112.132.13
802806806

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

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

p45cal

Well-known Member
Joined
Nov 10, 2009
Messages
4,763
Excel Workbook
ABCDEFGHIJKLM
1Receiving Date16-Nov-1226-Nov-1227-Dec-1228-Feb-1305-Mar-1322-Mar-1322-Mar-13
2ParametersUnitFatimaFatimaFatimaFatimaFatimaFatimaFatimaEngroSona/ffc
3Moisture%0.180.110.140.250.220.210.210.270.24
4Biuret%0.970.971.121.030.91111.020.9
5Mesh Size000
6+2.80 mm%2.56.52.125.73.83.85.83.3
7+2.40 mm%16.417.51413.617.720.420.417.110.6
8+2.00 mm%46.739.446.745.840.244.144.132.362.8
9+1.70 mm%25.331.12924.324.124.524.537.120.2
10+1.40 mm%5.64.55.2875561.8
11+1.00 mm%2.50.91.54.32.51.41.410.8
12+0.50 mm%0.80.10.81.71.90.70.70.60.4
13-0.50 mm%0.200.70.30.90.10.10.10.1
141 ~ 2.4 mm%96.593.496.49691.595.595.593.596.2
15Fines%10.11.522.80.80.80.70.5
16Avg. Prill Sizemm2.112.172.092.052.112.162.162.12.16
17Avg. Crush StrengthGrams8071291806806802817817812819
18ParametersUnitEngroEngroEngroEngroEngroEngro
19Moisture%0.210.140.220.320.240.27
20Biuret%1.030.80.991.190.941.02
21Mesh Size
22+2.80 mm%5.579.14.60.96.35.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.
 
Last edited:

p45cal

Well-known Member
Joined
Nov 10, 2009
Messages
4,763
You don't actually needall those $ symbols in the formula, they can be:
Excel Workbook
KLM
2FatimaEngroSona/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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

Board Regular
Joined
May 26, 2011
Messages
67

ADVERTISEMENT

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

Well-known Member
Joined
Nov 10, 2009
Messages
4,763
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

Board Regular
Joined
May 26, 2011
Messages
67

ADVERTISEMENT

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.........
 

azii

Board Regular
Joined
May 26, 2011
Messages
67
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-1226-11-1227-12-1228-02-1305-03-1322-03-13
ParametersUnitEngroEngro Engro Engro Engro Engro
Moisture% 0.210.14 0.220.32 0.240.27
Biuret% 1.030.8 0.991.19 0.941.02
Mesh Size
+2.80 mm% 5.579.1 4.6 0.9 6.3 5.8
+2.40 mm% 14.614.7 13.3 9.0 17.3 17.1
+2.00 mm% 34.24.7 34.2 28.4 36.1 32.3
+1.70 mm% 35.04.4 36.4 50.3 29.6 37.1
+1.40 mm% 8.40.3 9.4 6.0 8.5 6.0
+1.00 mm% 1.60.1 1.6 2.6 1.5 1.0
+0.50 mm% 0.50 0.4 1.8 0.5 0.6
-0.50 mm% 0.20 0.1 1.0 0.2 0.1
1 ~ 2.4 mm% 93.820.9 94.9 96.3 93.0 93.5
Fines% 0.70 0.5 2.8 0.7 0.7
Avg. Prill Sizemm 2.092.94 2.07 1.95 2.13 2.1
Avg. Crush StrengthGrams8194645 804801 806 812.0

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

Watch MrExcel Video

Forum statistics

Threads
1,133,264
Messages
5,657,715
Members
418,410
Latest member
CrishFergus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top