Help with creating an exam book.. - Page 2

#16 01-08-2006, 06:55 PM

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" <Sarrah.21c88y_1136760301.093@excelforum-nospam.com> wrote in
message news:Sarrah.21c88y_1136760301.093@excelforum-nospam.com...
>
> 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
>

Sarrah · #17 01-08-2006, 07:03 PM

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

pinmaster · #18 01-08-2006, 07:06 PM

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 · #19 01-08-2006, 07:08 PM

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)

#20 01-08-2006, 07:20 PM

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" <Sarrah.21c9em_1136761801.3727@excelforum-nospam.com> wrote in
message news:Sarrah.21c9em_1136761801.3727@excelforum-nospam.com...
>
> 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
>

pinmaster · #21 01-08-2006, 07:43 PM

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

Sarrah · #22 01-08-2006, 09:59 PM

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?

EDIT: EDIT: I need to store information from these cells on sheet1 (D7,F18,F19,F20,F21,F22,F23,F24,F25,D29) to starting from B6 to K65536 on sheet3.. All the cells except for D7 contain formula, I'm sure if we use paste special paste value then I'll be able to store the information, I tried but it isn't working, I think I'm doing something wrong not sure what tho, need help!!

#23 01-09-2006, 04:45 AM

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

#24 01-09-2006, 05:00 AM

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" <Sarrah.21ch9y_1136772001.0149@excelforum-nospam.com> wrote in
message news:Sarrah.21ch9y_1136772001.0149@excelforum-nospam.com...
>
> 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
>

#25 01-09-2006, 05:40 AM

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

Sarrah · #26 01-11-2006, 01:26 PM

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

pinmaster · #27 01-11-2006, 06:06 PM

Hi Sarrah,

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

Regards
JG

Welcome to the Excel Forum

Forum Statistics