Advanced Autofill Methods in Excel Hi, I have one Excel sheet that looks like th

Question

Advanced Autofill Methods in Excel

Hi,

I have one Excel sheet that looks like this:

This is a sheet of a training course that will run in a number of groups in various regions and to different genders.

Another sheet in the same Excel book details as follows:

Now, I want those fields under "Region" and "Date" columns to be automatically filled in the 2nd sheet when I enter the values under the "Group" column.

Is this possible? How?

Please note that sometimes the calendar on the first sheet could span the whole year and there might be groups running for months.

Region Gender Sep 1, 2014 Sep 2, 2014 Sep 3, 2014 Sep 4, 2014 Sep 5, 2014 Sep 6, 2014 Central Male C1 C2 Central Female C3 Western Male W1 Western Female W2

Explanation / Answer

$^&*
Hi,

Try this:"|

1. Suppose your base data is in range A1:H5 of sheet1. Headings are in A1:H1

2. In cell B2 of sheet2, enter this formula and copy down

=INDEX(Sheet1!$A$2:$A$5,SUMPRODUCT((Sheet1!$C$2:$H$5=Sheet2!$A2)*(ROW(Sheet1!$A$2:$A$5)-ROW(Sheet1!$A$1))),1)

3. In cell D2 of sheet2, enter this formula and copy down

=INDEX(Sheet1!$A$1:$H$1,1,SUMPRODUCT((Sheet1!$C$2:$H$5=Sheet2!$A2)*(COLUMN(Sheet1!$C$1:$H$1)+COLUMN(Sheet1!$B$1))))

Hope this helps.

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.