yogi_Search Sheets '1st' '2nd' etc. And Pull Information By Last Name Of Student

                                          Google Spreadsheet   Post  #1263
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun 26, 2013
user Heath Luehmann (http://productforums.google.com/forum/?zx=81uncl5s0y25#!category-topic/docs/spreadsheets/TVL6-nc_RcM)
How to search a spreadsheet and return values of the row
I have a database that holds student names, groups, leaders, and schedule on nine sheets.  I would love to be able to do a search over the entire database for something like "Veronica" which would return something like this:

Name: Veronica Smith

Group: 2 - C

Leader: Carol Johnson

9:00: Room 212

9:30: Room 301

10:00: Gym

10:30 Room 233

11:00 Snacks

And would also be able to list several options if there were more 'Veronicas' in the list.

I've been looking through formulas and scripts, but I have not found something that would work for this, yet.  Any input would be appreciated!  Thanks.

---

Thanks for being willing to help, Yogi.

a) Our church does a vacation Bible school that has almost a thousand kids that we need to keep track of.  We found that a google spreadsheet is so much better than emailing new spreadsheets every time there is a change.  We are also able to allow others to have viewing access of the document for different areas of the campus.  Much of this is done on phones (android and ios).  Finding the information is not always easy on the mobile devices, so I was considering a formula that would allow a search in a cell resulting with matches.  I also considered a script that would create a pop-up box with the information.  We would want to be able to search:

By a kid's name and get the crew, crew leader(s), and schedule

By a crew and get the kid, crew leader(s), and schedule

By a crew leader and get the kid, (other crew leader, if applicable), and schedule

Most of the examples I was looking at online were for very small amounts of specific data.  I would want to be able to search of any of these and get all options quickly.

b) The cell does not matter.  Maybe it would be best to put it in a separate sheet which could be embedded within a separate google site that would work well for mobile devices and display results.

c) I changed the data from this spreadsheet from a past year, but it would work for an example.  If I searched for Jacob, I would want to see:

Last Name: David

First Name: Jacob

Crew: K - F

Crew Leader: Marissa Gianino,

Lexie Schomaker

9:00: SW169

9:35: Sanctuary

10:00: Chadder

10:20 Snack

10:40 Bible

11:00 Games

11:20 Crafts

Last Name: Minton

First Name: Jacob

Crew: K - U

Crew Leader: Madeline Kaempfe,

Audrey Wind

9:00: SW213

9:35: Sanctuary

10:00: Chadder

10:20 Snack

10:40 Bible

11:00 Games

11:20 Crafts

https://docs.google.com/spreadsheet/ccc?key=0AiOyT7Zhl2dCdG9LQ0lKVm1nc3RYcld5OXpQX2djU2c#gid=2

Let me know what you think.  Thanks.

-Heath

-----------------------------------------------------------------------------------------------------------------------------------------------