Search For Student Names

[pareshshah]

Hi, could someone please create for me, a spreadsheet, where in the 'B' column I can search for things. For example in the 'B' column it will contain about 200 students names, so i would like a search bar/button on the spreadsheet where I can enter the students name and it will take me to that students name in the spreadsheet.

Thanks Paresh

Basically, on this spreadsheet, I would like the shape I have created to be clicked on and then it can search for students names under the 'Forename' category.


Moderator's Note: PLEASE take a moment and read the Forum Rules. The last thing we want to do is moderate your posts.

As per forum rules, this question was moved to a thread of its own.

[tigeravatar]

pareshshah,

Welcome to the forum!
Attached is a modified version of your posted workbook.
The shape has been assigned to this macro:

Please Login or Register  to view this content.

[pareshshah]

This is fantastic, however how do I make it so that all the cells do not shift up. For example now it shows the cells as 1,2,35,36,36 etc...

[tigeravatar]

pareshshah,

I'm afraid you lost me. What do you mean?

[pareshshah]

so example, when i type in to search for a name this works fine, but all the rows of data shift up a couple cells. How do i stop this from happening?

[tigeravatar]

If you're using the code I provided above, the cells aren't shifting, its just that non-matching cells get hidden. I guess that could be interpreted/viewed as "shifting up" though. If you don't want non-matches to be hidden, what do you want? highlight the rows that match?

[pareshshah]

Please take a look at the spreadsheet attached.

I have used your macro in the shape labelled 'search students'. Click this and enter a students name to search and you will find all of the other data on screen disappears. How can I fix this?

[tigeravatar]

Like I said, it becomes hidden. This is because the macro uses a filter. You can remove the filter by going to Data -> and clicking Filter to toggle the filter off. You could also click on the filter drop-down and check "(Select All)"
If you would rather not use the filter/hide rows solution, what would you like to do instead?

[pareshshah]

I am happy with the macro and how it works and performs. I would just like it if the rest of the information didnt disappear everytime I search for a students name

[tigeravatar]

pareshshah,

Attached is a modified version of the workbook you just posted.
I selected all of the shapes and went to the Drawing Tools Format tab and clicked on the More Options Arrow in the Size section -> Went to the Properties tab and selected "Don't move or size with cells" -> Close
See the attached Instructions.JPG image for a screenshot.

Now when you search for students, those buttons don't become hidden along with the rows. So you can just click the Show All Students button and all of the data will be displayed again.

[pareshshah]

Thats fantastic! Thank you so much,

Just one last thing, are you any good at creating macros to send a worksheet to various senders?

For example in the worksheet attached I would like only the specific worksheet to be sent to various heads of years from year 7 - year 11.

So basically,

1) There are 5 boxes each labelled 'Year 7' ' year 8' and so on....

2) I would like each of these boxes to be able to be clicked on and the current sheet ONLY to be sent to the different recipients.

3) Just for now you can call each individual email addresses '[email protected]' and '[email protected]'

4) The email should contain the attached spreadsheet and be sent via Outlook.

[tigeravatar]

Unfortunately I'm not familiar with how to email using a macro. And this questino is different enough that it warrants its own thread anyway. That way you'll get more visibility on the new question and someone who is familiar with that should be able to provide an answer.

[JBeaucaire]

If that takes care of your original query, please select Thread Tools from the menu above and mark the thread as solved. Thanks.