Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
INDEX and VLOOKUP not working
Hi - I'm trying to use a solution that Bernie provided earlier that
looks up a student's score data on a sheet and puts it into another report sheet. I've included my post and Bernie's solution below. The solution works for the first student (in row 13), but does not work for any other student from row 14 on down (there is a row between each student). Is it a problem with referencing or arrays? Any help would, again, be greatly appreciated. Thanks ============================== Hi there - I’ve made a gradebook that other teachers will now use, but we want to be able to print reports from it. Say if a teacher clicks anywhere on a student’s row, how can Excel output that student’s data, found in certain columns in that sheet as well as possibly 6 other worksheets (7 periods in a day) into Word, another Excel sheet, or some other format? If you can help, here’s the layout of the workbook: 8 sheets total, named Period 1, Period 2, …, Period 7, and Fields (where I put the HREF scores). Col headings in sheets 1 through 7 occupy rows 8 – 12. Student last and first names are in cols A and B, starting on row 13. The same student may be on each Period’s sheet. There is 1 row under each student’s row that also has data for that student (2 rows per student). Values of formulas to output are in cols E through Q, T, AH, and AI. Values in a range to also output start at AR—a new col is added after AR for each new assignment; scores are entered in the cells below. Another range to output starts 3 columns after the last column used for the assignments. A col will be entered each day with the date in the header and daily points in cells down below. Note that these 'ranges' will end up being in different columns in different periods. If this is possible, us teachers would also like it if we could output reports for all students in the entire workbook at once, similar to a mail merge. ============================== Solution by Bernie: I think you should consider a different solution: setting up report templates for each Period. Insert a new column A on each sheet, and in that column, enter a formula like this in A13 (whichever row has your first student name) =IF(B13="", A12 & " B", B13 & ", " & C13) That will create a one cell name for each student (and a second for the second row) that can be used to look up values. Then insert a new sheet, a report template for that sheet, and name a cell StudName. Then enter the student name of interest, in the form Lastname, Firstname Then for each report sheet, enter these formulas somewhere in a three cell block, like A3:A5, and then drag to the right as far as you can. These formulas will pull data based on the value in StudName =INDEX('Period 1'!$12:$12,1,Column(D1)) =VLOOKUP(StudName,'Period 1'!$6:$10000,COLUMN(D1),FALSE) =VLOOKUP(StudName & " B",'Period 1'!$6:$10000,COLUMN(D1),FALSE) Then, select and drag the cells around the page to lay the values out the way that you want. Delete the formulas that pull in unneeded data, and leave the formulas that will pull in data from the future. Set the page print area, the formatting, and then you can pull in any student's data at any time. Data from all periods can be combined into one report, etc - the key it that it will all be formula driven, linked to the entered student's name. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
complex index function not working | Excel Worksheet Functions | |||
IF THEN with Match & Index formula not working | Excel Worksheet Functions | |||
Tab Index Not working? | Excel Programming | |||
Index Match not working | Excel Worksheet Functions | |||
Index/Match not working | Excel Worksheet Functions |