VBA code that will get the desired data using datas on a column with diff. sheets

[richzter]

I tried watching tutorials and tried to do it.. i stumble upon vlookup but the tutorial i watched used the name manager with the offset formula.. i tried to mimic it but i got an error..
my project is kinda complicated.. well.. i think it is.. because im a beginner..

need help.. here is a an example of what im working.. this is only requirement number 1..

this is the link of the workbook...

and the rules are in here too..

https://app.box.com/s/cjjkgrctfor781ae5rmk

Thanks, i hope someone can help me

[richzter]

I tried watching tutorials and tried to do it.. i stumble upon vlookup but the tutorial i watched used the name manager with the offset formula.. i tried to mimic it but i got an error..
my project is kinda complicated.. well.. i think it is.. because im a beginner..

need help.. here is a an example of what im working.. this is only requirement number 1..

this is the link of the workbook...

and the rules are in here too..

https://app.box.com/s/cjjkgrctfor781ae5rmk

Thanks, i hope someone can help me

[richzter]

.....bump.....

[humdingaling]

unsure how this is VBA related....

Please Login or Register  to view this content.

[richzter]

unsure how this is VBA related....

Please Login or Register  to view this content.

thanks for helping.. can you please explain how it works? thanks

Ps.
if it uses the exact range only i think i cant use it.. i am asked to create a program that will get the value even if the table is messed up.. for example.. the user suddenly adds a column/row.. will it still work?

[humdingaling]

Please Login or Register  to view this content.

unrestricted range of the code

basis of the formula is
checked for Student number in table on "students Records" sheet
if it is not blank it directs does a vlookup on student number against table in "updates" Sheets

if it is blank, it takes the student Name and does vlookup against student record
surrounding this vlookup however is an error check
the assumption is if the student number is blank and the student name returns error on the vlookup it then makes the field blank . ie ""

[richzter]

Please Login or Register  to view this content.

unrestricted range of the code

basis of the formula is
checked for Student number in table on "students Records" sheet
if it is not blank it directs does a vlookup on student number against table in "updates" Sheets

if it is blank, it takes the student Name and does vlookup against student record
surrounding this vlookup however is an error check
the assumption is if the student number is blank and the student name returns error on the vlookup it then makes the field blank . ie ""

thanks..

but what if the second search is like this.. you need to use the student no. as a key but in the other sheet is named cleared student no.?
and the one you will get is Course Choice..

is there some way we can declare that?

[humdingaling]

don't quite understand what you are asking

you saying if table in updates does not have student number?

if this is what you can use IFERROR similar to the student name vlookup

[richzter]

don't quite understand what you are asking

you saying if table in updates does not have student number?

if this is what you can use IFERROR similar to the student name vlookup

... no i mean.. it does have student no. but different in column name/header

example.. im looking for id number.. but the other sheet doesnt have 1, it has Student number instead.. it has the same data's only it has a different header name.

[humdingaling]

sorry not sure what you are asking
can you provide sample data?

[richzter]

sorry not sure what you are asking
can you provide sample data?

i only tweeked some info..
https://app.box.com/s/cjjkgrctfor781ae5rmk

[humdingaling]

so you have added some which arent meant to show up?
isn't Rule 4.if no course is found using above rules course=blank

ie Buddy, Amy and Rinoa are blank
isnt that what is meant to happen?

Please Login or Register  to view this content.

also i have changed the code abit to not the table "name"

[richzter]

yeah some of it is meant to be blank.. actually i only changed the column header

[humdingaling]

yeah some of it is meant to be blank.. actually i only changed the column header

it made no difference in the vlookup?
because you are not looking up the header you are looking up the data set under the header

[richzter]

oh yeah.. i just tried.. but when i add some row and columns the value turns to 0..

[richzter]

Ps.
if it uses the exact range only i think i cant use it.. i am asked to create a program that will get the value even if the table is messed up.. for example.. the user suddenly adds a column/row.. will it still work?

sorry its true the column name doesnt matter haha silly me :P

[humdingaling]

i just read the original question and forgot you are only just learning vlookup

maybe this will help you conceptualise it
http://www.howtogeek.com/howto/13780...okup-in-excel/

if combines vlookup and if/isblank combination
only thing to remember is the table array is the important part of the vlookup and reference is really important

if you dont understand the $ signs and its application this is a really good link that explains it
http://www.cpearson.com/excel/relative.aspx

[richzter]

i cant use vlookup. because when the user adds multiple row/columns the value changes..

[humdingaling]

rows should not effect the result if you just put the whole column reference instead of a table reference
A1:C17 > should be A:C

Columns....you need to control this
unless you want to relative column index number

however this will require the headers and the placing of the headers will play have more relevance
ie

Please Login or Register  to view this content.

This formula will look for C8 which is "COURSE"
in update sheet...however in the update sheet you have it as "Course enrolled"
if you change header to course it will return value 2, of which you can combine into your vlookup to give

Please Login or Register  to view this content.

add Replicate into the main formula given in previous posts

Please Login or Register  to view this content.

the same needs to be done with your Student records ...but the header row on that sheet is row 3

Now if you insert columns in the update sheet
the TABLE array would update...but the column index number would change to "match" the header which in this case is "COURSE"

as you can see there is a lot you need to control and amend in order to make it work
and you do need to understand every bit of it in order to amend or make changes

[watiwawa]

Can you post the exact rules to make it clearer?