Help with creating an exam book..

Sarrah · #1 01-07-2006, 11:18 AM

Hello, I need help with creating an exam book with students results, I'm just a newbie and am not familiar with some of the functions, what I wanted to ask is how can I make a save button so that when its pressed macro will take students numbers and all the subject results including the average and store them in the list of results spreadsheet..

Thanks in advance

cvolkert · #2 01-07-2006, 01:10 PM

What you are asking for doesn't seem to difficult, but it would probably be helpful to see what type of input you are working with. It isn't clear where the info you want to put in the list is coming from. Maybe you could post a file or give some more info.

Sarrah · #3 01-07-2006, 03:57 PM

Okay, sorry for not explaining it well the first time, but i'll try this time..
we are allowed to have only three sheets in a spreadsheet, first is a form with students information (which we will get from students list by using their student no.) and also their exam result information, average %, a chart, save button, print button and add button which will take us to the student list to add more students if we wanted to and the second sheet should have the list of all the students in the class that have taken the exam and the third one is stored result list..

I think I know how to do the save function using micros but what i wanted to ask is how can I get student information from student list by typing student no on result form (sheet1) which in the end I'm going to store on result list sheet (sheet3)..

Sarrah · #4 01-07-2006, 07:53 PM

All I want to know is that what formula would I need to get all these informations from one sheet to another by typing just student no. thanks, I'll be waiting for the answer I really really do need help and quickly too..

#5 01-08-2006, 04:55 AM

Hi Sarah

With headers in row 1 on each sheet, and assuming the data on sheet2
starts with Student Number in Column A, and with data extending to
Column F, then maybe something like the following on Sheet1 cell B2

=VLOOKUP($A2,Sheet2!($A$2:$F$100,column(),0)
copied across through C2:F2, then the whole range B2:F2 copied down for
as many rows as required.
Change ranges to suit.

--
Regards

Roger Govier

"Sarrah" <Sarrah.21agtm_1136678100.8158@excelforum-nospam.com> wrote in
message news:Sarrah.21agtm_1136678100.8158@excelforum-nospam.com...
>
> All I want to know is that what formula would I need to get all these
> informations from one sheet to another by typing just student no.
> thanks, I'll be waiting for the answer I really really do need help
> and
> quickly too..
>
>
> --
> Sarrah
> ------------------------------------------------------------------------
> Sarrah's Profile:
> http://www.excelforum.com/member.php...o&userid=30223
> View this thread:
> http://www.excelforum.com/showthread...hreadid=499013
>

Sarrah · #6 01-08-2006, 10:17 AM

Thanks for the reply Roger!

I have one more question, I need to make a student list and a small form right above the list in the same sheet with fields empty so that I can enter information and press the macro button, which will result in storing all the information in rows under the form.. I tried to do it but I'm having trouble it seem to store on the first row every time I try to add a new information which should be added on the next row, you know what I mean?

#7 01-08-2006, 11:10 AM

Hi Sarrah

In your code for writing the data back to the sheet you will need to
determine where the lastrow is in column A with something like
lastrow = .Cells(Rows.Count, 1).End(xlUp).Row
then write the range of data to
Range("a" & lastrow)

--
Regards

Roger Govier

"Sarrah" <Sarrah.21bkvb_1136730001.4909@excelforum-nospam.com> wrote in
message news:Sarrah.21bkvb_1136730001.4909@excelforum-nospam.com...
>
> Thanks for the reply Roger!
>
> I have one more question, I need to make a student list and a small
> form right above the list in the same sheet with fields empty so that
> I
> can enter information and press the macro button, which will result in
> storing all the information in rows under the form.. I tried to do it
> but I'm having trouble it seem to store on the first row every time I
> try to add a new information which should be added on the next row,
> you
> know what I mean?
>
>
> --
> Sarrah
> ------------------------------------------------------------------------
> Sarrah's Profile:
> http://www.excelforum.com/member.php...o&userid=30223
> View this thread:
> http://www.excelforum.com/showthread...hreadid=499013
>

Sarrah · #8 01-08-2006, 12:40 PM

I'm sorry to be such a pain in the butt but I want to input information in the fields and when I press the button the information should store in the table under the form.. my problem is I cant seem to store it automatically in the row under the first data, it just keeps replacing that first data..

pinmaster · #9 01-08-2006, 01:08 PM

I'm not very good with VBA but I use something like this:

Range("B26").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True

Hope this helps!
JG

#10 01-08-2006, 01:15 PM

Hi Sarrah

So your data is being written to Column B, not column A.

In your code you need to determine the last used row in column B with
something like

lastrow = Range("B1").Cells(Rows.Count, 1).End(xlUp).Row

then write your data out to
Range("B"&lastrow + 1)

--
Regards

Roger Govier

"Sarrah" <Sarrah.21brky_1136738701.3904@excelforum-nospam.com> wrote in
message news:Sarrah.21brky_1136738701.3904@excelforum-nospam.com...
>
> I'm sorry to be such a pain in the butt but what I'm trying to do is a
> bit different I think.. I'm a bit lost with your explanation.. okay
> let
> me show you how my student list sheet looks like..
>
> click on the 'LINK HERE'
> (http://img490.imageshack.us/img490/6844/image22dn.jpg)
>
> I want to input information in the fields and when I press the button
> the information should store in the table under the form.. my problem
> is I cant seem to store it automatically in the row under the first
> data, it just keeps replacing that first data..
>
>
> --
> Sarrah
> ------------------------------------------------------------------------
> Sarrah's Profile:
> http://www.excelforum.com/member.php...o&userid=30223
> View this thread:
> http://www.excelforum.com/showthread...hreadid=499013
>

Sarrah · #11 01-08-2006, 05:50 PM

Ahh it worked, thanks for the help!!

I need help with one thing more, about Vlookup, I dunno how to do it I've tried what you told me before but it doesn't work oh and I don't have any header on any page.. what I want to do is, type a student number on first black and it should lookup all the value from sheet2 and give me information for name, age, ***, and group, how would I do that?

My Resultform sheet looks something like this:- CLICK HERE

pinmaster · #12 01-08-2006, 06:01 PM

Try this:

=VLOOKUP($A1,Sheet2!$B$26:$F$1000,COLUMN(B1),0)
copy across

HTH
JG

#13 01-08-2006, 06:10 PM

Hi Sarrah

My original Vlookup, assumed you were going across the page in your
form, not down the page.
It also assumed that you were starting your data in cell A2 of Sheet2,
which I don't think is the case, I think it starts with B26.

That being the case, then on your form enter the following into cell D8
=VLOOKUP($D$7,Sheet2!$B$26:$F$100,ROW(2:2),0)
Copy down through the cells below.

I have just noticed you have row 10 hidden. Why?
If that is the case, then the formula will not work as you copy down,
you may need to hard code each cell as you want the offset
(where I have ROW(2:2)) to be 2 for cell D8, 3 for D9, 4 for D11, 5 for
D12.

--
Regards

Roger Govier

"Sarrah" <Sarrah.21c5xm_1136757301.3412@excelforum-nospam.com> wrote in
message news:Sarrah.21c5xm_1136757301.3412@excelforum-nospam.com...
>
> Ahh it worked, thanks for the help!!
>
> I need help with one thing more, about Vlookup, I dunno how to do it
> I've tried what you told me before but it doesn't work oh and I don't
> have any header on any page.. what I want to do is, type a student
> number on first black and it should lookup all the value from sheet2
> and give me information for name, age, ***, and group, how would I do
> that?
>
> My Resultform sheet looks something like this:- 'CLICK HERE'
> (http://img417.imageshack.us/img417/8030/image20if.jpg)
>
>
> --
> Sarrah
> ------------------------------------------------------------------------
> Sarrah's Profile:
> http://www.excelforum.com/member.php...o&userid=30223
> View this thread:
> http://www.excelforum.com/showthread...hreadid=499013
>

Sarrah · #14 01-08-2006, 06:30 PM

Thanks for the reply, and no I didn't hide it, it some how got deleted or maybe I pressed a wrong button or something, see even after using excel for 3 months I'm still a newbie :P okay's I'm going to try it right now :D thanks for the help Roger!!! and Pinmaster also :D

Sarrah · #15 01-08-2006, 06:42 PM

Hi its me again, I did it your way and it works now but when my student number filed is left blank the other fields under student number look weird.. like this.. anyway to get rid of it?

CLICK HERE

Another error is that when I enter Number I get name on every cell where I pasted the formula instead of getting other information like age and *** etc, I changed the last part where it says ROW(2:2),0) to what pinmaster typed COLUMN(B1),0) then it worked :P

Welcome to the Excel Forum

Forum Statistics