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


--
Sarrah
------------------------------------------------------------------------
Sarrah's Profile: http://www.excelforum.com/member.php...o&userid=30223
View this thread: http://www.excelforum.com/showthread...hreadid=499013

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.


--
cvolkert
------------------------------------------------------------------------
cvolkert's Profile: http://www.excelforum.com/member.php...o&userid=24380
View this thread: http://www.excelforum.com/showthread...hreadid=499013

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
------------------------------------------------------------------------
Sarrah's Profile: http://www.excelforum.com/member.php...o&userid=30223
View this thread: http://www.excelforum.com/showthread...hreadid=499013

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

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" wrote in
message ...

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

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

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" wrote in
message ...

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

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

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


--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
View this thread: http://www.excelforum.com/showthread...hreadid=499013

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" wrote in
message ...

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

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)


--
Sarrah
------------------------------------------------------------------------
Sarrah's Profile: http://www.excelforum.com/member.php...o&userid=30223
View this thread: http://www.excelforum.com/showthread...hreadid=499013

Try this:

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

HTH
JG


--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
View this thread: http://www.excelforum.com/showthread...hreadid=499013

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" wrote in
message ...

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)


--
Sarrah
------------------------------------------------------------------------
Sarrah's Profile:
http://www.excelforum.com/member.php...o&userid=30223
View this thread:
http://www.excelforum.com/showthread...hreadid=499013

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
------------------------------------------------------------------------
Sarrah's Profile: http://www.excelforum.com/member.php...o&userid=30223
View this thread: http://www.excelforum.com/showthread...hreadid=499013

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)


--
Sarrah
------------------------------------------------------------------------
Sarrah's Profile: http://www.excelforum.com/member.php...o&userid=30223
View this thread: http://www.excelforum.com/showthread...hreadid=499013

Hi Sarrah

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


--
Regards

Roger Govier


"Sarrah" wrote in
message ...

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)


--
Sarrah
------------------------------------------------------------------------
Sarrah's Profile:
http://www.excelforum.com/member.php...o&userid=30223
View this thread:
http://www.excelforum.com/showthread...hreadid=499013

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


--
Sarrah
------------------------------------------------------------------------
Sarrah's Profile: http://www.excelforum.com/member.php...o&userid=30223
View this thread: http://www.excelforum.com/showthread...hreadid=499013

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


--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
View this thread: http://www.excelforum.com/showthread...hreadid=499013

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)


--
Sarrah
------------------------------------------------------------------------
Sarrah's Profile: http://www.excelforum.com/member.php...o&userid=30223
View this thread: http://www.excelforum.com/showthread...hreadid=499013

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.

--
Regards

Roger Govier


"Sarrah" wrote in
message ...

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


--
Sarrah
------------------------------------------------------------------------
Sarrah's Profile:
http://www.excelforum.com/member.php...o&userid=30223
View this thread:
http://www.excelforum.com/showthread...hreadid=499013

Hi Sarrah,
Here are the basics of Roger's formula
=IF(condition to look for,if True,if False)

condition to look for: $D$7="" (D7 is empty), which will result in a
TRUE or FALSE statement
if TRUE then: "" which means leave the cell blank (double quotes)
if FALSE then: VLOOKUP($D$7,Sheet2!$B$26:$F$100,20 )

Also, the double quotes leaves the cells blank but basically you can
put anything you want in between..... i.e "No Search";"Not Found", but
most people leave it blank.

Hope it makes things a little bit clearer for you. And BTW your english
is as good as mine.

Regards
JG


--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
View this thread: http://www.excelforum.com/showthread...hreadid=499013

Sarrah,

the information you have provided is still very sketchy, but perhaps
the following might help you. You ask how you can get student
information from student list by typing student number, so this implies
that you have student data along the lines of:

A B C D
Number Surname Forename D.O.B. etc
0001 Smith Jane
0003 Jones David
etc

You will probably have lots more information in this table, but you
want to type student number on one sheet and have some of the
information from the student list to appear. Assume that the table
above occupies cells A1 through to D500 in the sheet called "Students".
Assume further that you want to type a student number into cell A2 on
another sheet for results - the following formulae can be entered:

B2: =VLOOKUP(A2,Students!$A$2:$D$500,2,0)
C2: =VLOOKUP(A2,Students!$A$2:$D$500,3,0)
D2: =VLOOKUP(A2,Students!$A$2:$D$500,4,0)

B2 would return the surname of the student whose number is typed into
cell A2, and the forename would appear in C2 along with the date of
birth in cell D2. You could copy these formulae down and then enter
other student numbers in cells A3, A4, A5 etc - these would not have to
be in the same order as in the main table.

Hopefully, these comments will help you progress a bit further.

Pete

Ahh right, now I understand, Thanks Roger and Pinmaster for Explaining
it :D I tried and it worked!!! :D you guys are the best!!


Alright, One more problem, this would be the last question I promise!!

Okay, I'm trying to store information from sheet1 to sheet3 using macro
on a button but some of the cells in sheet1 contain formula (like
calculate percentage and average etc) how can I transfer these
information using macros?


--
Sarrah
------------------------------------------------------------------------
Sarrah's Profile: http://www.excelforum.com/member.php...o&userid=30223
View this thread: http://www.excelforum.com/showthread...hreadid=499013

Hi Sarrah

Firstly, let me re-iterate a comment from pinmaster as a result of a
comment you made to him.
Your English is Excellent!!!

I'm glad to have been able to help.
With regard to your last question (I bet it isn't <bg, especially if
you are going to continue to improve your Excel knowledge) then

use something like = Range("C1").value rather than = Range("C1")
when writing the data out.

--
Regards

Roger Govier


"Sarrah" wrote in
message ...

Ahh right, now I understand, Thanks Roger and Pinmaster for Explaining
it :D I tried and it worked!!! :D you guys are the best!!


Alright, One more problem, this would be the last question I promise!!

Okay, I'm trying to store information from sheet1 to sheet3 using
macro
on a button but some of the cells in sheet1 contain formula (like
calculate percentage and average etc) how can I transfer these
information using macros?


--
Sarrah
------------------------------------------------------------------------
Sarrah's Profile:
http://www.excelforum.com/member.php...o&userid=30223
View this thread:
http://www.excelforum.com/showthread...hreadid=499013

My posting seems totally out of place here - despite what it says, I
posted it on Jan 8 at 1:00am, and it just seems to have got lost for a
day!

Pete

Thanks, Roger, Pinmaster, :) and Pete I found your formula and the way
you explained to be very useful and easy to understand, thanks a lot!!
I think now I can use Vlookup formula in the future without any problem
:D


--
Sarrah
------------------------------------------------------------------------
Sarrah's Profile: http://www.excelforum.com/member.php...o&userid=30223
View this thread: http://www.excelforum.com/showthread...hreadid=499013

Hi Sarrah,

You're very welcome, and thanks for the excellent feedback.

Regards
JG


--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
View this thread: http://www.excelforum.com/showthread...hreadid=499013