Search Sheets Help

[Dishboy09]

I have an idea for a search sheet that i have no idea how to create, and am looking for help.

I would like Sheet 1 to just be a search box and you can type in an address, and below it will be a table that will show the results that match your search. The matched search data will be stored on sheet 2 with a large list of addresses.

How would i go about creating something like this??


Thanks!

 

[RasGhul]

Hi,

Here's a formula version, it will return rows that match from partial street address. Adjust the ranges to the size of your data set on sheet 2.

On the Search sheet make sure the formula is copied past maybe 500 rows past what the total results should be.

Book1
	B	C	D
2	Partial Search	Matches #
3	Mar	7
4
5	Search Results
6
7	Street	Suburb	Postcode
8	10 Mary St1	Port Adelaide1	5015
9	12 March St1	Port Adelaide3	5015
10	13 Mary St1	Port Adelaide4	5015
11	4 Margeret	Port Adelaide5	5015
12	16 Mary St1	Port Adelaide7	5015
13	66 Marigold Ave	Port Adelaide10	5015
14	20 Mary St1	Port Adelaide11	5015
Sheet1

Cell Formulas
Range	Formula
C3	=COUNTIF(Sheet2!D:D,1)
B8	{=IF(ROWS(Sheet1!$B$8:B8)>$C$3,"",INDEX(Sheet2!A$2:A$10,SMALL(IF(Sheet2!$D$2:$D$10=1,ROW(Sheet2!$A$2:$A$10)-ROW(Sheet2!$A$2)+1),ROWS(Sheet1!$B$8:B8))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Book1
	A	B	C	D
1	Street	Suburb	Postcode	Partial Match
2	10 Mary St1	Port Adelaide1	5015	1
3	2 Mickey Place	Disneyland	5015
4	12 March St1	Port Adelaide3	5015	1
5	13 Mary St1	Port Adelaide4	5015	1
6	4 Margeret	Port Adelaide5	5015	1
7	10 Peppercorn Drive	Salisbury	5015
8	16 Mary St1	Port Adelaide7	5015	1
9	66 Marigold Ave	Port Adelaide10	5015	1
10	20 Mary St1	Port Adelaide11	5015	1
Sheet2

Cell Formulas
Range	Formula
D2	=IF(ISNUMBER(SEARCH(Sheet1!$B$3,Sheet2!A2)),1,"")

 

[Dishboy09]

Hi,

Here's a formula version, it will return rows that match from partial street address. Adjust the ranges to the size of your data set on sheet 2.

On the Search sheet make sure the formula is copied past maybe 500 rows past what the total results should be.

Book1
	B	C	D
2	Partial Search	Matches #
3	Mar	7
4
5	Search Results
6
7	Street	Suburb	Postcode
8	10 Mary St1	Port Adelaide1	5015
9	12 March St1	Port Adelaide3	5015
10	13 Mary St1	Port Adelaide4	5015
11	4 Margeret	Port Adelaide5	5015
12	16 Mary St1	Port Adelaide7	5015
13	66 Marigold Ave	Port Adelaide10	5015
14	20 Mary St1	Port Adelaide11	5015
Sheet1

Cell Formulas
Range	Formula
C3	=COUNTIF(Sheet2!D:D,1)
B8	{=IF(ROWS(Sheet1!$B$8:B8)>$C$3,"",INDEX(Sheet2!A$2:A$10,SMALL(IF(Sheet2!$D$2:$D$10=1,ROW(Sheet2!$A$2:$A$10)-ROW(Sheet2!$A$2)+1),ROWS(Sheet1!$B$8:B8))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Book1
	A	B	C	D
1	Street	Suburb	Postcode	Partial Match
2	10 Mary St1	Port Adelaide1	5015	1
3	2 Mickey Place	Disneyland	5015
4	12 March St1	Port Adelaide3	5015	1
5	13 Mary St1	Port Adelaide4	5015	1
6	4 Margeret	Port Adelaide5	5015	1
7	10 Peppercorn Drive	Salisbury	5015
8	16 Mary St1	Port Adelaide7	5015	1
9	66 Marigold Ave	Port Adelaide10	5015	1
10	20 Mary St1	Port Adelaide11	5015	1
Sheet2

Cell Formulas
Range	Formula
D2	=IF(ISNUMBER(SEARCH(Sheet1!$B$3,Sheet2!A2)),1,"")

I really appreciate the response, but i have no idea what to do with this or where to put it to make it work?

 

[RasGhul]

When the website was upgraded this code doesnt work. I'll post a link to my mock up sheet for you soon.

 

[RasGhul]

Here's a link to the sample version I made,

Adjust the ranges to the size of your data set on sheet 2.

On the Search sheet make sure the formula is copied past maybe 500 rows past what the total results should be.


Search Sheets Help_Dishboy09.xlsx