Best filtering / replication method of data from one tab to others (used for student records)

[stevenfletcher]

I am using Excel to store database items for university classes. Each class has 4 groups each with a TA (teaching assistant). There are about 30 students / class. Each group has 7 to 9 students. Every week there are 2 or 3 scores for each student. Every month there are an additional 2 or 3 separate monthly scores.


Generally I use 6 tabs in the Excel file:


1) A global tab with resources for the whole semester (which is static but has lookup data which changes when the week number is incremented)

2) A class (whole class) tab where the grades are entered

3) A tab for each group where grades are displayed and where a mini lesson plan is displayed


For each TA I print out his or her group each week. For each group I want to squeeze the data on one landscaped sheet. Each group sheet has a little header information, followed by the student information in 7 to 10 rows, followed by a mini lesson plan for the day.


The problem is what is the best way to automatically filter the student information for the group print outs. I have used 3 approaches. All work but each has disadvantages because excel is not a real database. Here are the 3 approaches i have tried:


1) Create a calculated copy (using =) of the data on to each group tab. Then use a filter on the group number (which is listed in each student row). [The problem with this is that it’s a little flakey if you add data to the class tab (like if you add another student and now have an additional row)]

2) Use VLOOKUP to get the data after insuring that you have more than enough rows. [The problem here is if you copy and paste the VLOOKUP formulas. The absolute references are ok, but the column number does not increment when you copy so you have to enter these very carefully and you have to hand craft every cell and then not add any new cells. The risk feels very high here.]

3) Create an additional tab that is a calculated copy (using =) of the data in the class tab. Then for each group insure there are more rows than are needed (I’m using 12 rows now) and then copy and paste from the calculated copy for each group.
In all the above approaches there is a lot of hand work and thus a lot of risk. Ultimately the student’s grades are determined from this. I have used the filter approach this semester until I got 3 new students (one in each of 3 groups). After two hours of work I went to solution #3 above.

Is there some better way to do this?

 

[Andrew Poulsom]

Welcome to MrExcel.

You can make VLOOKUP's column index relative to the current column. Say your lookup table is in columns A:C and the lookup value is in D1. In E1 enter:

=VLOOKUP($D1,$A:$C,COLUMNS($D1:E1),FALSE)

and copy to F1.

To get the best from your data it should be in first normal form on a single worksheet (with a column for class). Then you can use Excel's inbuilt data analysis tools like AutoFilter and Pivot Tables. If you convert your data into a Table it will automatically expand as you add new data.

 

[stevenfletcher]

Welcome to MrExcel.

You can make VLOOKUP's column index relative to the current column. Say your lookup table is in columns A:C and the lookup value is in D1. In E1 enter:

=VLOOKUP($D1,$A:$C,COLUMNS($D1:E1),FALSE)

and copy to F1.

To get the best from your data it should be in first normal form on a single worksheet (with a column for class). Then you can use Excel's inbuilt data analysis tools like AutoFilter and Pivot Tables. If you convert your data into a Table it will automatically expand as you add new data.

Andrew, thank you. I will try your suggestions. I did not know you could control VLOOKUP to force it to change the cell references relatively.

I tried the "table" command and could not see a difference. I tried it first on the "group" tab (the calculated tab) and then tried it on the "class" tab (the source of the data). In both cases when I added a row in the source tab (the "class" tab) it did not automatically add itself into the group tab. Can you explain a little more about this.

At the moment I have no clue about what are pivot tables, but I'll search more and try this too.

I did find an interesting work around. On the "group" tab I re-positioned everything so that all of the "non row like" things are at the top of the page and that the student records (and their headers) are the last thing on the page. Then using the " = " command I could show the whole class and then use a filter on the group number. This works well and will expand "somewhat automatically" (sometimes you have to change the filter back to all and then back again to the specific group if you have added a row in the source ("class") tab)

One thing I have no idea about is the memory use of " = " versus the VLOOKUP command / function.

 

[Andrew Poulsom]

I didn't say that adding a row to a table would automatically update another worksheet. I just said that the table would automatically expand when new data is added.

What's the " = " command?

 

[stevenfletcher]

I didn't say that adding a row to a table would automatically update another worksheet. I just said that the table would automatically expand when new data is added.

What's the " = " command?

Thanks Andrew. The adding a row was my main problem. Perhaps I didn't make it clear in the first post.

The " = " command (I don't know if it has another name) is where you type " =A1 " or " =AB32 " in a cell (no quotation marks) to replicate the data from one cell into another.