Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array formula lookup
Hi, I am new to array formulas, but so far I am thoroughly impressed with what they can do. I am stuck right now on one and was hoping for some help. I am a teacher and I keep daily records of my students on excel. My daily record spreadsheet is similar to this: Row 1 - Dates for each school day Column A - Student Names B2:CA50 - Daily Record keeping such as absence, tardy, homework, etc. What I want is a column that will list all absences for a specific day. In other words, have a cell where I can input any date and a list would generate for all the students absent on that date. The formula would need to search row 1 for the date, find all "A"s in the column of that date, and return the name in column A. Can this be done? Thanks for any replies. CJ-22 -- CJ-22 ------------------------------------------------------------------------ CJ-22's Profile: http://www.excelforum.com/member.php...o&userid=17551 View this thread: http://www.excelforum.com/showthread...hreadid=506632 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array formula lookup
Hi!
Here's one way: A51 = date criteria Array entered: =INDEX(A$2:A$50,SMALL(IF(INDEX(B$2:CA$50,,MATCH(A$ 51,B$1:CA$1,0))="a",ROW(A$2:A$50)-ROW(A$2)+1),ROWS($1:1))) Copy down until you get #NUM! errors meaning the data has been exhausted. We can suppress the display of the #NUM! errors using an error trap in the formula. This would make the formula twice as long and add to its complexity. An alternative method is to use conditional formatting to hide the errors. Select the cells that hold the formula Goto FormatConditional Formatting Formula is: =(ISERROR(cell_ref) Click the Format button Set the font color to be the same as the background color OK out Biff "CJ-22" wrote in message ... Hi, I am new to array formulas, but so far I am thoroughly impressed with what they can do. I am stuck right now on one and was hoping for some help. I am a teacher and I keep daily records of my students on excel. My daily record spreadsheet is similar to this: Row 1 - Dates for each school day Column A - Student Names B2:CA50 - Daily Record keeping such as absence, tardy, homework, etc. What I want is a column that will list all absences for a specific day. In other words, have a cell where I can input any date and a list would generate for all the students absent on that date. The formula would need to search row 1 for the date, find all "A"s in the column of that date, and return the name in column A. Can this be done? Thanks for any replies. CJ-22 -- CJ-22 ------------------------------------------------------------------------ CJ-22's Profile: http://www.excelforum.com/member.php...o&userid=17551 View this thread: http://www.excelforum.com/showthread...hreadid=506632 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array formula lookup
Typo correction:
Formula is: =(ISERROR(cell_ref) Should be: Formula is: =ISERROR(cell_ref) Biff "Biff" wrote in message ... Hi! Here's one way: A51 = date criteria Array entered: =INDEX(A$2:A$50,SMALL(IF(INDEX(B$2:CA$50,,MATCH(A$ 51,B$1:CA$1,0))="a",ROW(A$2:A$50)-ROW(A$2)+1),ROWS($1:1))) Copy down until you get #NUM! errors meaning the data has been exhausted. We can suppress the display of the #NUM! errors using an error trap in the formula. This would make the formula twice as long and add to its complexity. An alternative method is to use conditional formatting to hide the errors. Select the cells that hold the formula Goto FormatConditional Formatting Formula is: =(ISERROR(cell_ref) Click the Format button Set the font color to be the same as the background color OK out Biff "CJ-22" wrote in message ... Hi, I am new to array formulas, but so far I am thoroughly impressed with what they can do. I am stuck right now on one and was hoping for some help. I am a teacher and I keep daily records of my students on excel. My daily record spreadsheet is similar to this: Row 1 - Dates for each school day Column A - Student Names B2:CA50 - Daily Record keeping such as absence, tardy, homework, etc. What I want is a column that will list all absences for a specific day. In other words, have a cell where I can input any date and a list would generate for all the students absent on that date. The formula would need to search row 1 for the date, find all "A"s in the column of that date, and return the name in column A. Can this be done? Thanks for any replies. CJ-22 -- CJ-22 ------------------------------------------------------------------------ CJ-22's Profile: http://www.excelforum.com/member.php...o&userid=17551 View this thread: http://www.excelforum.com/showthread...hreadid=506632 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array formula lookup
In case you might be interested in also creating a list of "Tardy", say you
enter the date of interest in CB1, and in CB2 you enter your "A" for absent, or "T" for tardy, or whatever. Then enter this *array* formula in CB3, and copy down enough rows to insure that all possible names are returned. You'll get a #NUM! error when the formula runs out of names to find. =INDEX($A$2:$A$50,SMALL(IF(($B$1:$CA$1=$CB$1)*($B$ 2:$CA$50=$CB$2),ROW($A$1:$ A$49)),ROWS($1:1))) -- Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "CJ-22" wrote in message ... Hi, I am new to array formulas, but so far I am thoroughly impressed with what they can do. I am stuck right now on one and was hoping for some help. I am a teacher and I keep daily records of my students on excel. My daily record spreadsheet is similar to this: Row 1 - Dates for each school day Column A - Student Names B2:CA50 - Daily Record keeping such as absence, tardy, homework, etc. What I want is a column that will list all absences for a specific day. In other words, have a cell where I can input any date and a list would generate for all the students absent on that date. The formula would need to search row 1 for the date, find all "A"s in the column of that date, and return the name in column A. Can this be done? Thanks for any replies. CJ-22 -- CJ-22 ------------------------------------------------------------------------ CJ-22's Profile: http://www.excelforum.com/member.php...o&userid=17551 View this thread: http://www.excelforum.com/showthread...hreadid=506632 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array formula lookup
Thanks for the responses. They have worked well for me. However, this experience has prompted me to want to take it a step further. Is it possible to generate a list of all students with a specific comment and list them in the order that the comments were given? For example: Column 1 = names Row 1 = dates B2:CA74 = records. I want to generate a list of all students who have the letter "m" (missing assignments) in their row. The previous formulas worked great for generating a list based upon a specific column (date), but I now want a list to be continuous throughout the whole grading term (B2:CA74). I want to list all students who have missing assignments and I want them to be in order by dates. I hope I have explained this clearly. Thank you for any replies. -- CJ-22 ------------------------------------------------------------------------ CJ-22's Profile: http://www.excelforum.com/member.php...o&userid=17551 View this thread: http://www.excelforum.com/showthread...hreadid=506632 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array formula lookup
Hey Ragdyer, Your array formula works well, but I have another problem I want to solve. In some cases, there might be more than one comment in a cell. For example, there might be a "t" for tardy and an "m" for missing assignments. The formula as is will find all the single comments, but if there is a cell that has multiple comments "tm" (tardy/missing assignments) it only returns an error. Can this formula be modified to find all the t's even if the cell contains a t and an m? -- CJ-22 ------------------------------------------------------------------------ CJ-22's Profile: http://www.excelforum.com/member.php...o&userid=17551 View this thread: http://www.excelforum.com/showthread...hreadid=506632 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array formula lookup
Thought I would add the formula I am currently using (thanks RagDyer and Biff). =INDEX($A$1:$A$426,SMALL(IF(($D$1:$BX$1=$CB$2)*($D $1:$BX$426=$CB$1),ROW(A$1:$A$425)),1)) CB2 = date to find from row 1 CB1 = letter to find from corresponding column In each cell there might be more than one letter (ame), but I still need to find the letter *a* if it is in that cell and return the name from column 1. How can I make this formula find all a's even if there is more than one letter in the cell? Thanks for any replies. -- CJ-22 ------------------------------------------------------------------------ CJ-22's Profile: http://www.excelforum.com/member.php...o&userid=17551 View this thread: http://www.excelforum.com/showthread...hreadid=506632 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions | |||
lookup with a formula | Excel Worksheet Functions | |||
Array Formula Limits (around 2800 or so) ? Lotus DSUM has no problems with it ! | Excel Discussion (Misc queries) | |||
Lookup Array Formula | Excel Worksheet Functions | |||
How do I lookup a value in a array that is not in ascending order | Excel Worksheet Functions |