Solved

VBA/EXCEL/Running Records for Teachers

Posted on 2002-03-16
16
1,010 Views
Last Modified: 2007-12-19
Hello,
  I am using Excel 2000 on Windows 98 working with an existing spreadsheet trying to modify it for better use among teachers. I have the existing file here for you to download: http://connes.wcpss.net/Excel/
I would like to know if there is a way and/or formula to have the "running records data" sheet gather information on what the teacher entered for the students show up on the "individual" sheet. At the same time I do not want it to bring up the books which have no information entered or old data. For example, if I entered data for "Abe" and it was on books 31-32 (and levels 1-2 & 3-4 from a earlier date in the school year), I would like only Books 31-32 to show up when I enter Abe's name on the individual sheet. Abe's most recent book is Fortune's Friend(Book level 31-32)(He is basically done being tested) and Katherine's most recent book level is Nick's Glasses (Book level 7-8). Of course, we would do this type of sorting throughout the year to see the progress of the class and to create groups to do instruction on the students instructional level.  I will have the end-user enter the date of when they actually took the assessment so there will be a cell with the date in it. We could do a sort on the "most recent date" with a formula or use VBA to accomplish this.  Can you think of a formula Or a solution for this problem?

Thanks,
Ron
0
Comment
Question by:RWahlen
  • 7
  • 5
  • 4
16 Comments
 
LVL 44

Expert Comment

by:bruintje
Comment Utility
Hi RWahlen,

you could delete the question vb because it's the same and i guess your answer will show up in this thread sooner

:O)Bruintje
0
 

Author Comment

by:RWahlen
Comment Utility
Bruintje,

Thanks for the tip -I deleted the question in VB and hope to get an answer soon!
0
 
LVL 44

Expert Comment

by:bruintje
Comment Utility
already looking into it and i got a question

what you're trying to do here is
-get some running totals for the individual
-on the individual sheet
-from the running records data sheet

is that correct?

:O)Bruintje
0
 

Author Comment

by:RWahlen
Comment Utility
Bruintje,

Yes, some of the goals you mentioned are correct.
One the the objectives will be for the teacher to keep
a history of the records (from lower level books), but
be able to pull the most current records from the whole
class. We could not come up with a formula for this
problem.  We take assessment records from the
class (about 30 students) throughout the year. We need
the old records, from Sept/Oct/Nov/Dec/ and now that it is
almost April, we will want to pull only records from the
end of March to group the students into appropriate
reading groups. Does this help explain what we are
looking for?

Thanks,
Ron

0
 
LVL 17

Expert Comment

by:calacuccia
Comment Utility
Let me try to restate, Ron,

You want on worksheet "Individual", from row 5 to row (number of students + 5 -1),

the information about the student entered in column A of that row, from the worksheet "running records data", but only the latest book worked on, and not showing the blank books neither.

Is that correct?

If yes, where should that information be positioned on sheet "Inidividual"?

calacuccia
0
 

Author Comment

by:RWahlen
Comment Utility
Yes Calacuccia!

This is the only information
that we want to move over to the
individual spreadsheet. The individual
spreadsheet needs to be re-done. There
will have to be a place where the user
can type in a name of the student and
this information will be pulled over:


1. title of book
2. date
3. AR
4. M
5. s
6. V
7. R


Thanks!
Ron
0
 
LVL 44

Expert Comment

by:bruintje
Comment Utility
that's a lot more specific ;)
0
 
LVL 17

Expert Comment

by:calacuccia
Comment Utility
It is a bit clearer at least, Ron. ;-)

But, the "running records data" sheet will have to be redone as well probably. From book 11-12 on, there is no date column yet, which is important for the 'to be used' formula to pull records from this sheet. As you will add date columns, the to be retrieved columns will be shifted to the right, and the formula must probably take this into account.

Especially, this bothers me, since I would probably use the column '#E' of each book to determine if there is data present or not.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:RWahlen
Comment Utility
Calacuccia,  

I am not very clear on how exactly you would use column '#E' to determine if there is data present or not. Why would you use column '#E'?

I tried to put the running records sheet into a sheet which has the data across the columns so that I could use a function to read (i.e.) columns B-AK. But this didn't work well with all of the data we need to collect.

Ron
0
 
LVL 17

Expert Comment

by:calacuccia
Comment Utility
Ron,

I would use columns #E because it is the first column of each book which is empty if the student did not take the book yet

I am not referring to column E but to columns D, M, V, ... and al the other columns which have value #E on line 6 of it.

calacuccia
0
 

Author Comment

by:RWahlen
Comment Utility
Calacuccia,

Is it possible for you to show me this in the Excel file and email it to me.

Thanks,
Ron
0
 
LVL 17

Expert Comment

by:calacuccia
Comment Utility
Ron,

I'll paste my work instead.

I've developped the following UDF for you (UDF = User Defined Function). This is a VBA function written for use on your Excel worksheet.

It has 2 arguments,

mName which is the name of the student, and
mArgument which is a value of 1 to 7 indicating what data needs to be retrieved.

You call it like this, on your individual sheet, for example in cell B5, with Abe filled in in A5

=LastBook(A5,1)

The function must be pasted in your module1 of your VBA part, and is below my signature.

Any questions, please ask back,

calacuccia

Function LastBook(mName As String, mArgument As Integer) As Variant
Dim RRS As Worksheet
Dim aRow As Long, aCol As Integer
Set RRS = ThisWorkbook.Worksheets("running records data")
On Error Resume Next
aRow = Application.WorksheetFunction.Match(mName, RRS.Range("A:A"), 0)
If Err.Number <> 0 Then
    Err.Clear
    GoTo ErrHandler
End If
On Error GoTo 0
For j = RRS.Range("IV6").End(xlToLeft).Column To 2 Step -1
    If RRS.Cells(6, j) = "#E" Then
        If RRS.Cells(aRow, j) <> "" Then
            aCol = j
            j = 1
        End If
    End If
Next j
If j = 1 Then GoTo NoData
Select Case mArgument
Case 1 'Title of Book
    LastBook = RRS.Cells(5, aCol - 1)
Case 2 'Date
    LastBook = RRS.Cells(aRow, aCol - 2)
Case 3 'AR
    LastBook = RRS.Cells(aRow, aCol + 2)
Case 4 'M
    LastBook = RRS.Cells(aRow, aCol + 3)
Case 5 'S
    LastBook = RRS.Cells(aRow, aCol + 4)
Case 6 'V
    LastBook = RRS.Cells(aRow, aCol + 5)
Case 7 'R
    LastBook = RRS.Cells(aRow, aCol + 6)
End Select
Exit Function
'Last part is only in case of errors
ErrHandler:
LastBook = "Error"
Exit Function
NoData:
LastBook = "Empty"
End Function



0
 
LVL 44

Expert Comment

by:bruintje
Comment Utility
Hi RWahlen,

Any luck yet to try the suggestions in this thread? Please provide some feedback to the experts willing to spend their time and try to solve your problem.

Thanks
:O)Bruintje
0
 

Author Comment

by:RWahlen
Comment Utility
Bruintje,

I did receive the code from Calacuccia but I do not know where in excel to place it.

Thanks,
Ron
0
 
LVL 44

Accepted Solution

by:
bruintje earned 200 total points
Comment Utility
Hi again Ron,

It's been a while, if this problem is still acute for you then you can send it to me i'll take a look at it.

mulbum@worldonline.nl

Brian
0
 

Author Comment

by:RWahlen
Comment Utility
Brian,

This problem could still use some work. I am unfamiliar at this time how to enter code or VBA into Excel. Perhaps, if you can explain this to me - I will be able to solve this one.

Thanks,
Ron
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
My experience with Windows 10 over a one year period and suggestions for smooth operation
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now