Conditional List Display help

[rck3]

So I have been working on a sheet (multiple sheets actually) and was just thrown a curve. I currently have a list where users selection a city and the data populates (formula below). Pretty straight forward though I am sure there is an easier way. I was asked to create this list based so it displays either the data base on a city selected or a region now.

I am at a loss on how to condition this formula so it will display all the data for the city if a city is selected or to display all the data for the region which would include multiple cities.

=IF(ISERROR(INDEX('Shelter Data'!$A$6:$H$262,MATCH(0,COUNTIF($A$6:$A6,'Shelter Data'!$A$6:$A$262)+('Shelter Data'!$C$6:$C$262<>$C$3),0),COLUMN(A5)))," ",(INDEX('Shelter Data'!$A$6:$H$262,MATCH(0,COUNTIF($A$6:$A6,'Shelter Data'!$A$6:$A$262)+('Shelter Data'!$C$6:$C$262<>$C$3),0),COLUMN(A5))))

 

[Akashwani]

Hi,

Welcome to MrExcel.

The data is obviously different, but the formulas will do as you require, is this what you envisage....

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Serial Number	Pattern	Size	Coating	Location A	Condition	Location B	Condition	Location C	Condition
2	Roll1234	XX	1200 x 1000	AAA	12/04/2011	Stage1	*	*	*	*
3	Roll1235	Pretty	1200 x 900	BBB	11/04/2011	Stage1	*	*	*	*
4	Roll1236	Dotted	1200 x 1000	CCC	02/04/2011	Stage1	09/04/2011	Stage2	*	*
5	Roll1237	XYZ	1200 x 900	AAA	22/03/2011	Stage1	29/03/2011	Stage2	01/04/2011	Finished
6	Roll1238	Tartan	1200 x 1000	AAA	10/03/2011	Stage1	27/03/2011	Stage2	02/04/2011	Finished
7	Roll1239	Stripe	1200 x 900	BBB	12/04/2011	Stage1	15/04/2011	Stage2	*	*
8	Roll1240	Circle	1200 x 1000	CCC	11/04/2011	Stage1	*	*	*	*
9	Roll1241	Raised	1200 x 900	AAA	12/03/2011	Stage1	26/03/2011	Stage2	05/04/2011	Finished
10	Roll1242	Twisted	1200 x 1000	AAA	18/04/2011	Stage1	*	*	*	*
Sheet1

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
6	*	Enter a Criteria	*	*	*	*	*	*	*	*	*	*
7	5	AAA	Serial Number	Pattern	Size	Coating	Location A	Condition	Location B	Condition	Location C	Condition
8	*	*	Roll1234	XX	1200 x 1000	AAA	12/04/2011	Stage1	00/01/1900	0	00/01/1900	0
9	*	*	Roll1237	XYZ	1200 x 900	AAA	22/03/2011	Stage1	29/03/2011	Stage2	01/04/2011	Finished
Sheet2

Or this....

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
6	*	Enter a Criteria	*	*	*	*	*	*	*	*	*	*
7	3	Finished	Serial Number	Pattern	Size	Coating	Location A	Condition	Location B	Condition	Location C	Condition
8	*	*	Roll1237	XYZ	1200 x 900	AAA	22/03/2011	Stage1	29/03/2011	Stage2	01/04/2011	Finished
9	*	*	Roll1238	Tartan	1200 x 1000	AAA	10/03/2011	Stage1	27/03/2011	Stage2	02/04/2011	Finished
10	*	*	Roll1241	Raised	1200 x 900	AAA	12/03/2011	Stage1	26/03/2011	Stage2	05/04/2011	Finished
Sheet2

I hope this helps to guide you in the right direction.

Check out these videos....
http://www.youtube.com/user/ExcelIsFun#p/search/7/vAEPXVf3uOs
http://www.youtube.com/user/ExcelIsFun#p/search/16/R5ZWAiNJLNo

Good luck

 

[rck3]

Thanks, it does give me a new direction to look. If you look at your table, what I would want to do is allow the user to select a Size type (say Size 1200X1000) which will list all the details of those matching that Size (assumung a secondary condition is blank) but then allow users to select a secondary match of Coating Type (say Coating AAA) to show a listing of just those of that are 1200X1000 and AAA. Is this possible? I know I am pushing excel here (I wanted to design and have it done in sql).

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD>*</TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="FONT-WEIGHT: bold">Serial Number</TD><TD style="FONT-WEIGHT: bold">Pattern</TD><TD style="FONT-WEIGHT: bold">Size</TD><TD style="FONT-WEIGHT: bold">Coating</TD><TD style="FONT-WEIGHT: bold">Location A</TD><TD style="FONT-WEIGHT: bold">Condition</TD><TD style="FONT-WEIGHT: bold">Location B</TD><TD style="FONT-WEIGHT: bold">Condition</TD><TD style="FONT-WEIGHT: bold">Location C</TD><TD style="FONT-WEIGHT: bold">Condition</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>Roll1234</TD><TD>XX</TD><TD>1200 x 1000</TD><TD>AAA</TD><TD style="TEXT-ALIGN: right">12/04/2011</TD><TD>Stage1</TD><TD>*</TD><TD>*</TD><TD>*</TD><TD>*</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>Roll1235</TD><TD>Pretty</TD><TD>1200 x 900</TD><TD>BBB</TD><TD style="TEXT-ALIGN: right">11/04/2011</TD><TD>Stage1</TD><TD>*</TD><TD>*</TD><TD>*</TD><TD>*</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>Roll1236</TD><TD>Dotted</TD><TD>1200 x 1000</TD><TD>CCC</TD><TD style="TEXT-ALIGN: right">02/04/2011</TD><TD>Stage1</TD><TD style="TEXT-ALIGN: right">09/04/2011</TD><TD>Stage2</TD><TD>*</TD><TD>*</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD>Roll1237</TD><TD>XYZ</TD><TD>1200 x 900</TD><TD>AAA</TD><TD style="TEXT-ALIGN: right">22/03/2011</TD><TD>Stage1</TD><TD style="TEXT-ALIGN: right">29/03/2011</TD><TD>Stage2</TD><TD style="TEXT-ALIGN: right">01/04/2011</TD><TD>Finished</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD>Roll1238</TD><TD>Tartan</TD><TD>1200 x 1000</TD><TD>AAA</TD><TD style="TEXT-ALIGN: right">10/03/2011</TD><TD>Stage1</TD><TD style="TEXT-ALIGN: right">27/03/2011</TD><TD>Stage2</TD><TD style="TEXT-ALIGN: right">02/04/2011</TD><TD>Finished</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD>Roll1239</TD><TD>Stripe</TD><TD>1200 x 900</TD><TD>BBB</TD><TD style="TEXT-ALIGN: right">12/04/2011</TD><TD>Stage1</TD><TD style="TEXT-ALIGN: right">15/04/2011</TD><TD>Stage2</TD><TD>*</TD><TD>*</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD>Roll1240</TD><TD>Circle</TD><TD>1200 x 1000</TD><TD>CCC</TD><TD style="TEXT-ALIGN: right">11/04/2011</TD><TD>Stage1</TD><TD>*</TD><TD>*</TD><TD>*</TD><TD>*</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD>Roll1241</TD><TD>Raised</TD><TD>1200 x 900</TD><TD>AAA</TD><TD style="TEXT-ALIGN: right">12/03/2011</TD><TD>Stage1</TD><TD style="TEXT-ALIGN: right">26/03/2011</TD><TD>Stage2</TD><TD style="TEXT-ALIGN: right">05/04/2011</TD><TD>Finished</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD>Roll1242</TD><TD>Twisted</TD><TD>1200 x 1000</TD><TD>AAA</TD><TD style="TEXT-ALIGN: right">18/04/2011</TD><TD>Stage1</TD><TD>*</TD><TD>*</TD><TD>*</TD><TD>*</TD></TR></TBODY></TABLE>

 

[Akashwani]

Hi,

I'm not sure how you would achieve that, but take a look here.....

http://www.youtube.com/user/ExcelIsFun#p/search/7/NqO3iekP1VY
http://www.youtube.com/user/ExcelIsFun#p/search/1/dUL3Cj9w52g

Good luck.