Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function Help
I would like to create a function that would return the last date a student
attended class. The spreadsheet is setup like below: Dates *LastDateAttended 7/1 7/2 7/3 ----------------------------------------------------------------------- Student1 No Show Student2 7/3 1.5 2.5 2 Student3 7/2 2.5 Any suggestions? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function Help
try the MAX() worksheet function .
"stacyjhaskins" wrote: I would like to create a function that would return the last date a student attended class. The spreadsheet is setup like below: Dates *LastDateAttended 7/1 7/2 7/3 ----------------------------------------------------------------------- Student1 No Show Student2 7/3 1.5 2.5 2 Student3 7/2 2.5 Any suggestions? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function Help
Hi,
Basic assumtions, starts in A1 and goes out only to Z1 with dates. This will search for the last date and put it in Col B, so the layout looks like this: Student Last 7/1/2005 ...... 7/24/05 <--( Z1) #1 Lastdate #2 Etc. Sub Macro2() Range("B2").Select Do Until ActiveCell.Offset(0, -1).Value = "" For y = 1 To 24 Z = ActiveCell.Offset(0, y).Value If Z = "" Then Else ReturnDate = ActiveCell.Offset(-ActiveCell.Row + 1, y).Value End If Next y ActiveCell.Value = ReturnDate ActiveCell.Offset(1, 0).Select Loop End Sub Hope this helps. Thanks, "stacyjhaskins" wrote: I would like to create a function that would return the last date a student attended class. The spreadsheet is setup like below: Dates *LastDateAttended 7/1 7/2 7/3 ----------------------------------------------------------------------- Student1 No Show Student2 7/3 1.5 2.5 2 Student3 7/2 2.5 Any suggestions? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function Help
Put this in each cell in the LastDateAttended column and add the user-defined
function below:_ =INDIRECT(CHAR(LastDate(ROW())+64) & "1") I am assuming you find the LastDateAttended by looking for the last entry in each student's row i.e the 1.5. 2.0 etc data, and taking the corresponding date from row 1. Function LastDate(r) LastData = Cells(r, Columns.Count).End(xlToLeft).Column End Function HTH "stacyjhaskins" wrote: I would like to create a function that would return the last date a student attended class. The spreadsheet is setup like below: Dates *LastDateAttended 7/1 7/2 7/3 ----------------------------------------------------------------------- Student1 No Show Student2 7/3 1.5 2.5 2 Student3 7/2 2.5 Any suggestions? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function Help
Sorry ... typo LastData should be LastDate
Function LastDate(r) LastDate = Cells(r, Columns.Count).End(xlToLeft).Column End Function "Toppers" wrote: Put this in each cell in the LastDateAttended column and add the user-defined function below:_ =INDIRECT(CHAR(LastDate(ROW())+64) & "1") I am assuming you find the LastDateAttended by looking for the last entry in each student's row i.e the 1.5. 2.0 etc data, and taking the corresponding date from row 1. Function LastDate(r) LastData = Cells(r, Columns.Count).End(xlToLeft).Column End Function HTH "stacyjhaskins" wrote: I would like to create a function that would return the last date a student attended class. The spreadsheet is setup like below: Dates *LastDateAttended 7/1 7/2 7/3 ----------------------------------------------------------------------- Student1 No Show Student2 7/3 1.5 2.5 2 Student3 7/2 2.5 Any suggestions? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function Help
Another solution to allow for dates outside range A1 to Z1:
=INDIRECT(LastDateColumn(ROW())) in LastDateAttended column Function LastDateColumn(r) Dim i as integer i = Cells(r, Columns.Count).End(xlToLeft).Column LastDateColumn = Cells(1, i).Address End Function HTH "Toppers" wrote: Sorry ... typo LastData should be LastDate Function LastDate(r) LastDate = Cells(r, Columns.Count).End(xlToLeft).Column End Function "Toppers" wrote: Put this in each cell in the LastDateAttended column and add the user-defined function below:_ =INDIRECT(CHAR(LastDate(ROW())+64) & "1") I am assuming you find the LastDateAttended by looking for the last entry in each student's row i.e the 1.5. 2.0 etc data, and taking the corresponding date from row 1. Function LastDate(r) LastData = Cells(r, Columns.Count).End(xlToLeft).Column End Function HTH "stacyjhaskins" wrote: I would like to create a function that would return the last date a student attended class. The spreadsheet is setup like below: Dates *LastDateAttended 7/1 7/2 7/3 ----------------------------------------------------------------------- Student1 No Show Student2 7/3 1.5 2.5 2 Student3 7/2 2.5 Any suggestions? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function Help
On Mon, 18 Apr 2005 21:00:03 -0700, "stacyjhaskins"
wrote: I would like to create a function that would return the last date a student attended class. The spreadsheet is setup like below: Dates *LastDateAttended 7/1 7/2 7/3 ----------------------------------------------------------------------- Student1 No Show Student2 7/3 1.5 2.5 2 Student3 7/2 2.5 Any suggestions? You can use a worksheet function. Assume the list of dates is in row 5 and is in ascending order, Student 1 is in row 7, and the dates are in columns D:Z. The array-entered formula: =IF(COUNT(D7:Z7)=0,"No Show",INDEX($A$5:$Z$5,1, MAX(ISNUMBER(D7:Z7)*COLUMN(D7:Z7)))) will return the date associated with the last entry. ***To enter an array formula, after copying or pasting in the above, hold down <ctrl<shift while hitting <enter. Excel will place braces {...} around the formula. --ron |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function Help
Your formula looks it would be simple to follow. I pluged in my cell
references, =IF(COUNT(X33:IV33)=0,"No Show",INDEX($X$29:$IV$29,1,MAX(ISNUMBER(X33:IV33)* COLUMN(X33:IV33)))) ,but I got #value! as a result. Did I type something wrong? Thanks in advance for your help. "Ron Rosenfeld" wrote: On Mon, 18 Apr 2005 21:00:03 -0700, "stacyjhaskins" wrote: I would like to create a function that would return the last date a student attended class. The spreadsheet is setup like below: Dates *LastDateAttended 7/1 7/2 7/3 ----------------------------------------------------------------------- Student1 No Show Student2 7/3 1.5 2.5 2 Student3 7/2 2.5 Any suggestions? You can use a worksheet function. Assume the list of dates is in row 5 and is in ascending order, Student 1 is in row 7, and the dates are in columns D:Z. The array-entered formula: =IF(COUNT(D7:Z7)=0,"No Show",INDEX($A$5:$Z$5,1, MAX(ISNUMBER(D7:Z7)*COLUMN(D7:Z7)))) will return the date associated with the last entry. ***To enter an array formula, after copying or pasting in the above, hold down <ctrl<shift while hitting <enter. Excel will place braces {...} around the formula. --ron |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function Help
Yes, that is what I'm trying to do. Where do I type the cell references to
allow the function to know where my dates are located? Do I need to type something in the ROW()? My dates are located in X29:IV29, and the students' names start in row 33. Thank you for your help. "Toppers" wrote: Another solution to allow for dates outside range A1 to Z1: =INDIRECT(LastDateColumn(ROW())) in LastDateAttended column Function LastDateColumn(r) Dim i as integer i = Cells(r, Columns.Count).End(xlToLeft).Column LastDateColumn = Cells(1, i).Address End Function HTH "Toppers" wrote: Sorry ... typo LastData should be LastDate Function LastDate(r) LastDate = Cells(r, Columns.Count).End(xlToLeft).Column End Function "Toppers" wrote: Put this in each cell in the LastDateAttended column and add the user-defined function below:_ =INDIRECT(CHAR(LastDate(ROW())+64) & "1") I am assuming you find the LastDateAttended by looking for the last entry in each student's row i.e the 1.5. 2.0 etc data, and taking the corresponding date from row 1. Function LastDate(r) LastData = Cells(r, Columns.Count).End(xlToLeft).Column End Function HTH "stacyjhaskins" wrote: I would like to create a function that would return the last date a student attended class. The spreadsheet is setup like below: Dates *LastDateAttended 7/1 7/2 7/3 ----------------------------------------------------------------------- Student1 No Show Student2 7/3 1.5 2.5 2 Student3 7/2 2.5 Any suggestions? |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function Help
I don't care to know the most hours a student stayed in a day, which is what
I believe the MAX() function would do. I'm trying to find the last date they attended (signified by the first value greater than 0 if working backwards starting with column IV) The function needs to return the date located near the top of the column. Any other ideas? Thanks for your suggestion. "Patrick Molloy" wrote: try the MAX() worksheet function . "stacyjhaskins" wrote: I would like to create a function that would return the last date a student attended class. The spreadsheet is setup like below: Dates *LastDateAttended 7/1 7/2 7/3 ----------------------------------------------------------------------- Student1 No Show Student2 7/3 1.5 2.5 2 Student3 7/2 2.5 Any suggestions? |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function Help
On Tue, 19 Apr 2005 22:04:03 -0700, "stacyjhaskins"
wrote: Your formula looks it would be simple to follow. I pluged in my cell references, =IF(COUNT(X33:IV33)=0,"No Show",INDEX($X$29:$IV$29,1,MAX(ISNUMBER(X33:IV33) *COLUMN(X33:IV33)))) ,but I got #value! as a result. Did I type something wrong? Thanks in advance for your help. I'm not sure. I can't reproduce the error with what I think are your inputs. Did you enter this as an array formula with <ctrl<shift<enter or did you just enter it as a normal formula with <enter? If that does not correct it, (and on my sheet it does not cause an error), send me a copy of the worksheet and I'll see what's going on. You may email it to me at: moc.enilonodlefnesorTAzyx Reverse the above and make the obvious substitution. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy of excel file not showing formulal/function in the function b | Excel Discussion (Misc queries) | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions | |||
Adding a custom function to the default excel function list | Excel Programming |