Make a Student Information System Spreadsheet

Here is a very basic student information system. So we have the name of the student here, their image

their ID, their birth date, gender, age, contact, and all this other information. Then we also have

the subjects here in the subject tab. So we have the same names of the students and also all the

subjects that they are taking and the grades they had in term one. Now what we want to do from here

is to get the information for each of those students. So we're going to choose the student

from here then show all their details in there and also show all their scores in there then here

we're going to put their image now luckily for us this is something that is very easy to do

we're going to use the combination of the xlookup and the drop down so first of all let's put a

drop down in here of all the students so just go to data then go to data validation and here you

We'll say add rule. Here on the criteria, we're going to click it and choose drop down from range

Then we just going to click here and we going to go to the student tab here and select this entire range up to the last student Then from there we can click OK And if you want you can edit the options down here on advanced options and change this one from the chip to the arrow

Then press done. Then we'll go back to the student here and you can see there's a drop down here

So when you click on this drop down, you can pick on any of the students here

So let's say pick this one. Once we do that, we want to see all their details here, there and also their image here

So let's go ahead and use the XLOOKUP to get the image here

So here we're just going to say equals XLOOKUP. For the search key here, we're going to pick the drop down

For the lookup range, we're going to go to our student information and we'll pick all

the names from here, like so. For the result range, we're going to pick the images

So this column all the way up to the last student, like so

and just cross the function, enter, and we have our image like that

So let me just center this image, just click on it, then on the align

just put it on the center like that. There we have it. So you'll see when we change from the drop down here

we pick another person, you can see the image changes as well

Can do that again So this is what we want Now we want to do the same for all these results So just get the ID here using the XLOOKUP So say equals XLOOKUP Again the search key is the

drop down. The lookup range will be these names and the resulting range will be the ID. Close

the function and we have our ID there. Again, when we change, the ID changes as well. So to get the

birthday will do the same. We use the XLOOKUP. Again this is a search term. The

lookup array will be these names again and the result to be the date of birth

There we have our birthday. So just quickly fill all of them using the same

process. Okay so I've used the XLOOKUP for all of these fields to get the

values here. So when we change the name there you can see that the information

changes as well, even the photo changes. You can do it again and it updates accordingly

like that. Now we also want to show the results here for each of these students. So again

we do the same process we use the XLOOKUP we say equals XLOOKUP the search term is the drop down here The lookup range you going to go to the subjects it the names that are here and the result range for English it this one You cross the function enter

and you can see the result there. If we change, it changes like that. So again, to get the scores

for the rest of the subjects, I'll use the XLOOKUP. Let me just do it quickly

there we go. So just using the xlookup and the drop down, we're able to get all the details for

the students and also all the scores. So I'll change the name here, pick this one. So you can

see their details there, you can see their scores, you can also see their image there. So for Gentriche

for example, you can see birthday is 19 December. When we go to the student information, Gentriche

is here and for sure it's 19 December so you can see all the information updates

for one person like that. It makes it easy if you just want to yze the

information for one student. It's a very simple student information system but

you can add more things to make it robust. So that's it for this video. If you

love such tutorials, subscribe to the channel. Thank you so much for watching

I'll see you in the next one