Search data on another sheet using VBA

[cjvdg]

Hello! I'm new with this area. I just want to ask if it is possible for me to use a search function in excel? So, as you can see in the attached file, I have a search area in the HOME sheet. I like to use it and get the data on the STUDENT INFO sheet then after clicking the search button, data will be displayed on the HOME sheet. Is that even possible in excel? If yes, can someone help me with it? Thank you in advance!
Enable GingerCannot connect to Ginger Check your internet connection
or reload the browserDisable in this text fieldEditEdit in GingerEdit in Ginger×

[JKwan]

Give this a go. I removed your MERGE CELL formats. They reek havoc

[Paul_Hossler]

I used events and data validation to make it easier to use (IMO )

I also did some re-laying-out and started in A1, since (again IMO) it's better to do it that way since it causes fewer problems/complications downstream

Capture.JPG

[p45cal]

…and without code.
There is data validation in cell E5 of the HOME sheet but it's not needed, just there to make it easier for me to test.

[cjvdg]

I'm going to try and explore all your solution. Thank you so much! Enable GingerCannot connect to Ginger Check your internet connection
or reload the browserDisable in this text fieldEditEdit in GingerEdit in Ginger×

[Paul_Hossler]

Enable GingerCannot connect to Ginger Check your internet connection
or reload the browserDisable in this text fieldEditEdit in GingerEdit in Ginger×

1. I still don't understand the above

2. Which ever approach you decide on, remember the big picture: names will be added and deleted so you don't want to have to keep modifying hard coded ranges, columns, etc.