# autofill cells by entering date

#### azii

##### Board Regular
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...)

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>

### Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

#### p45cal

##### Well-known Member
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
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
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
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
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
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

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

#### azii

##### Board Regular
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

##### Well-known Member
try the file here

Replies
3
Views
215
Replies
3
Views
303
Replies
4
Views
231
Replies
4
Views
705
Replies
2
Views
164

1,171,630
Messages
5,876,543
Members
433,199
Latest member
guerin47

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

### Which adblocker are you using?

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

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