Conditional List Display help

rck3

New Member
Joined
Mar 9, 2011
Messages
18
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))))
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Hi,

Welcome to MrExcel.

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


Excel Workbook
ABCDEFGHIJ
1Serial NumberPatternSizeCoatingLocation AConditionLocation BConditionLocation CCondition
2Roll1234XX1200 x 1000AAA12/04/2011Stage1****
3Roll1235Pretty1200 x 900BBB11/04/2011Stage1****
4Roll1236Dotted1200 x 1000CCC02/04/2011Stage109/04/2011Stage2**
5Roll1237XYZ1200 x 900AAA22/03/2011Stage129/03/2011Stage201/04/2011Finished
6Roll1238Tartan1200 x 1000AAA10/03/2011Stage127/03/2011Stage202/04/2011Finished
7Roll1239Stripe1200 x 900BBB12/04/2011Stage115/04/2011Stage2**
8Roll1240Circle1200 x 1000CCC11/04/2011Stage1****
9Roll1241Raised1200 x 900AAA12/03/2011Stage126/03/2011Stage205/04/2011Finished
10Roll1242Twisted1200 x 1000AAA18/04/2011Stage1****
Sheet1



Excel Workbook
ABCDEFGHIJKL
6*Enter a Criteria**********
75AAASerial NumberPatternSizeCoatingLocation AConditionLocation BConditionLocation CCondition
8**Roll1234XX1200 x 1000AAA12/04/2011Stage100/01/1900000/01/19000
9**Roll1237XYZ1200 x 900AAA22/03/2011Stage129/03/2011Stage201/04/2011Finished
Sheet2


Or this....


Excel Workbook
ABCDEFGHIJKL
6*Enter a Criteria**********
73FinishedSerial NumberPatternSizeCoatingLocation AConditionLocation BConditionLocation CCondition
8**Roll1237XYZ1200 x 900AAA22/03/2011Stage129/03/2011Stage201/04/2011Finished
9**Roll1238Tartan1200 x 1000AAA10/03/2011Stage127/03/2011Stage202/04/2011Finished
10**Roll1241Raised1200 x 900AAA12/03/2011Stage126/03/2011Stage205/04/2011Finished
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
 
Upvote 0
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>
 
Upvote 0

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top