Function Help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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,
 
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
 
Sorry ... typo LastData should be LastDate


Function LastDate(r)
LastDate = Cells(r, Columns.Count).End(xlToLeft).Column
End Function
 
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
 
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?
Click to expand...


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
 
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.
 
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.
 
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.
 
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.
Click to expand...

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
 
Back
Top