Solved

VBA/EXCEL/Running Records for Teachers

Posted on 2002-03-16
16
1,014 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
ID: 6873581
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
ID: 6873834
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
ID: 6873836
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
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 

Author Comment

by:RWahlen
ID: 6874672
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
ID: 6877218
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
ID: 6877442
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
ID: 6877474
that's a lot more specific ;)
0
 
LVL 17

Expert Comment

by:calacuccia
ID: 6877725
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
 

Author Comment

by:RWahlen
ID: 6878813
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
ID: 6880597
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
ID: 6880791
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
ID: 6882146
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
ID: 6923722
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
ID: 6925157
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
ID: 7019561
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
ID: 7033529
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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 view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

776 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