Google Spreadsheet Searching in Telephone Directory

Question:

( by HAM10 )

I've transferred a telephone directory created in Excel to Google Sheets with approx 2400 entries.

The entries are arranged alphabetically with some heading information in the lines immediately above each specific group of letters.

I'm trying to set it up similar to the Google calendar (wish there were a directory app) for embedding in website as I have done with the calendar.

I'd like a user to click on a letter in a list of letters at the top of the directory and have the display go to that area of the spreadsheet.

I'm aware of the functions "filter" and "query" but don't grasp how to use either.

Any guidance will be sincerely appreciated.

Thanx
Harvey

=================

Hello Kishan:

I've done as you suggested, taken a representative portion of the sheet and created a new sheet that is shared.

I sent a response but not sure if it went as expected, so I'm posting this response as well.

Below is a copy of that message in case it was never received.

Thank you,

Harvey

I’ve taken a few dozen lines from the original spreadsheet and made another which is accessible with the following link (hopefully).

My thought is to insert a line at the very top of the spreadsheet such as:

*A* *B* *C* etc

The user clicks on the letter representing the last name and the display is moved to that section of the spreadsheet, or

Perhaps something like ENTER THE FIRST FEW CHARACTERS OF THE LAST NAME:  Here the user does exactly that and the view

Moves to the first record that matches.

 I’m new to the Google applications and also wonder if the SHEETS application is not the best way to go to accomplish this.

 I appreciate any and all assistance you may provide – Thank you!

Solution:


I would suggest to insert a new sheet in your Spreadsheet, and you can name it 'Search Sheet'. Have a look at the following Screenshot of my 'Search Sheet':

Now put any search term like "abe" in Cell B1 as you can see in the above screenshot and put the following formula in Cell A3:

=iferror(query(arrayformula(upper('Phone Directory'!A:E));"select * where Col1 contains '" & upper(B1) & "'");"No Records Found...")

The above formula will fetch all the results from Sheet 'Phone Directory' that has "abe" as a part of string in Column A.


I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan.

iGoogleDrive