Help with creating an exam book..

[Sarrah]

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]

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]

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]

All I want to know is that what formula would I need to get all thes
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 an
quickly too.

 

[Roger Govier]

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.

 

[Sarrah]

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?

 

[Roger Govier]

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)

 

[Sarrah]

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..

 

[pinmaster]

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

 

[Roger Govier]

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)

 

[Sarrah]

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, sex, and group, how would I do
that?

My Resultform sheet looks something like this:- 'CLICK HERE'
(http://img417.imageshack.us/img417/8030/image20if.jpg)

 

[pinmaster]

Try this:

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

HTH
JG

 

[Roger Govier]

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.

 

[Sarrah]

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 okay's I'm going to try it
right now thanks for the help Roger!!! and Pinmaster also

 

[Sarrah]

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' (http://img408.imageshack.us/img408/4143/image49ev.jpg)

 

[Roger Govier]

Hi Sarrah

Forgot to wrap it in an IF() statement.
=IF($D$7="","",VLOOKUP($D$7,Sheet2!$B$26:$F$100,20))

 

[Sarrah]

Ah but what should I put between the inverted coma's.... I'm sorry if
this sounds dumb!

 

[pinmaster]

Hi Sarrah,
If row 10 is not hidden, then it is simply too narrow to see. Select
rows 7 thru 12, now click and hold the cursor between row 7 and 8 and
move very slightly up or down and let go, row 10 should now be visible
and the same height as the others, if it is not visible then it is
hidden, select rows 9 thru 11, right click and select "unhide".

HTH
JG

 

[Sarrah]

Hello pinmaster, I unhid it before typing in an formula they work only
if I tyype something in student number field if i leave it empty I get
an error message like #N/A.. Roger suggested to use IF statement, I'm
finding it a bit difficult.. (btw, excuse my english, it's not too
good)

 

[Roger Govier]

Hi Sarrah

Put nothing between the 2 double quotes.
Two successive double quotes with nothing in between is the Null string,
which basically says leave the cell empty.