Return first, second

[SimonHughes]

I want to select a unit by height and return all the width options available.

Unit height will be selected via a drop down box and then I want to create another drop down that will reference ONLY the width options available to the selected height. So drop down 1 will select say; 1020 and drop down 2 will only give options 1010 & 1310. Your help much appreciated


<TABLE style="WIDTH: 747pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=996><COLGROUP><COL style="WIDTH: 75pt; mso-width-source: userset; mso-width-alt: 3657" width=100><COL style="WIDTH: 48pt" span=14 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 75pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=100>Unit Height (mm)</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64>610</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64>730</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64>730</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64>730</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64>1020</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64>1020</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64>1360</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64>1360</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64>1360</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64>1670</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64>1670</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64>1970</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64>1970</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64>2400</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>Unit Width (mm)</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>690</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>780</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>1010</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>1310</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>1010</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>1310</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>1360</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>1560</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>1860</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>1860</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>2400</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>1860</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>2400</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>2400</TD></TR></TBODY></TABLE>

 

[Robert Mika]

Look here:
http://www.contextures.com/xldataval13.html

 

[How_Do_I]

I can start you off with this but as I’m not brilliant with excel, somebody will need to finish it for you.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
You need a list of unique Heights for the first Data Validation List.
<o></o>
B5 copied down will give you that. A5 is the formula to find the unique heights.
<o></o>
C5 needs to be copied down to give a count of the number of different widths for that height.
<o></o>
D5 copied down and across will give you the widths for each height.
<o></o>
Hope that starts you off and can anyone finish this for us please?

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 106px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><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><TD>K</TD><TD>L</TD><TD>M</TD><TD>N</TD><TD>O</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>Unit Height (mm)</TD><TD style="TEXT-ALIGN: right">610</TD><TD style="TEXT-ALIGN: right">730</TD><TD style="TEXT-ALIGN: right">730</TD><TD style="TEXT-ALIGN: right">730</TD><TD style="TEXT-ALIGN: right">1020</TD><TD style="TEXT-ALIGN: right">1020</TD><TD style="TEXT-ALIGN: right">1360</TD><TD style="TEXT-ALIGN: right">1360</TD><TD style="TEXT-ALIGN: right">1360</TD><TD style="TEXT-ALIGN: right">1670</TD><TD style="TEXT-ALIGN: right">1670</TD><TD style="TEXT-ALIGN: right">1970</TD><TD style="TEXT-ALIGN: right">1970</TD><TD style="TEXT-ALIGN: right">2400</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>Unit Width (mm)</TD><TD style="TEXT-ALIGN: right">690</TD><TD style="TEXT-ALIGN: right">780</TD><TD style="TEXT-ALIGN: right">1010</TD><TD style="TEXT-ALIGN: right">1310</TD><TD style="TEXT-ALIGN: right">1010</TD><TD style="TEXT-ALIGN: right">1310</TD><TD style="TEXT-ALIGN: right">1360</TD><TD style="TEXT-ALIGN: right">1560</TD><TD style="TEXT-ALIGN: right">1860</TD><TD style="TEXT-ALIGN: right">1860</TD><TD style="TEXT-ALIGN: right">2400</TD><TD style="TEXT-ALIGN: right">1860</TD><TD style="TEXT-ALIGN: right">2400</TD><TD style="TEXT-ALIGN: right">2400</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>Unique Height</TD><TD>List</TD><TD>Count</TD><TD style="TEXT-ALIGN: center">Width 1</TD><TD style="TEXT-ALIGN: center">Width 2</TD><TD style="TEXT-ALIGN: center">Width 3</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right">7</TD><TD style="TEXT-ALIGN: right">610</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">690</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD> </TD><TD style="TEXT-ALIGN: right">730</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">780</TD><TD style="TEXT-ALIGN: right">1010</TD><TD style="TEXT-ALIGN: right">1310</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD> </TD><TD style="TEXT-ALIGN: right">1020</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">1010</TD><TD style="TEXT-ALIGN: right">1310</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD> </TD><TD style="TEXT-ALIGN: right">1360</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">1360</TD><TD style="TEXT-ALIGN: right">1560</TD><TD style="TEXT-ALIGN: right">1860</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD> </TD><TD style="TEXT-ALIGN: right">1670</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">1860</TD><TD style="TEXT-ALIGN: right">2400</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD> </TD><TD style="TEXT-ALIGN: right">1970</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">1860</TD><TD style="TEXT-ALIGN: right">2400</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD> </TD><TD style="TEXT-ALIGN: right">2400</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">2400</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>A5</TD><TD>{=SUM(IF(FREQUENCY(IF(B1:O1<>"",MATCH("~"&B1:O1,B1:O1&"",0)),COLUMN(B1:O1)-COLUMN(B1)+1),1))}</TD></TR><TR><TD>B5</TD><TD>{=IF(ROWS(B$5:B5)>$A$5,"",INDEX($B$1:$O$1,,MATCH(0,COUNTIF($B$1:$O$1,"<"&$B$1:$O$1)-SUM(COUNTIF($B$1:$O$1,"="&B$4:B4)),0)))}</TD></TR><TR><TD>C5</TD><TD>=IF(B5="","",COUNTIF($B$1:$O$1,B5))</TD></TR><TR><TD>D5</TD><TD>{=IF(COLUMNS($D5:D5)>$C5,"",INDEX($B$2:$O$2,,SMALL(IF($B$1:$O$1=$B5,COLUMN($B$1:$O$1)-COLUMN($B$1)+1),COLUMNS($D5:D5))))}</TD></TR><TR><TD>E5</TD><TD>{=IF(COLUMNS($D5:E5)>$C5,"",INDEX($B$2:$O$2,,SMALL(IF($B$1:$O$1=$B5,COLUMN($B$1:$O$1)-COLUMN($B$1)+1),COLUMNS($D5:E5))))}</TD></TR><TR><TD>F5</TD><TD>{=IF(COLUMNS($D5:F5)>$C5,"",INDEX($B$2:$O$2,,SMALL(IF($B$1:$O$1=$B5,COLUMN($B$1:$O$1)-COLUMN($B$1)+1),COLUMNS($D5:F5))))}</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[T. Valko]

I can start you off with this but as I’m not brilliant with excel, somebody will need to finish it for you.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
You need a list of unique Heights for the first Data Validation List.
<o></o>
B5 copied down will give you that. A5 is the formula to find the unique heights.
<o></o>
C5 needs to be copied down to give a count of the number of different widths for that height.
<o></o>
D5 copied down and across will give you the widths for each height.
<o></o>
Hope that starts you off and can anyone finish this for us please?

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 106px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><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><TD>K</TD><TD>L</TD><TD>M</TD><TD>N</TD><TD>O</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD>Unit Height (mm)</TD><TD style="TEXT-ALIGN: right">610</TD><TD style="TEXT-ALIGN: right">730</TD><TD style="TEXT-ALIGN: right">730</TD><TD style="TEXT-ALIGN: right">730</TD><TD style="TEXT-ALIGN: right">1020</TD><TD style="TEXT-ALIGN: right">1020</TD><TD style="TEXT-ALIGN: right">1360</TD><TD style="TEXT-ALIGN: right">1360</TD><TD style="TEXT-ALIGN: right">1360</TD><TD style="TEXT-ALIGN: right">1670</TD><TD style="TEXT-ALIGN: right">1670</TD><TD style="TEXT-ALIGN: right">1970</TD><TD style="TEXT-ALIGN: right">1970</TD><TD style="TEXT-ALIGN: right">2400</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD>Unit Width (mm)</TD><TD style="TEXT-ALIGN: right">690</TD><TD style="TEXT-ALIGN: right">780</TD><TD style="TEXT-ALIGN: right">1010</TD><TD style="TEXT-ALIGN: right">1310</TD><TD style="TEXT-ALIGN: right">1010</TD><TD style="TEXT-ALIGN: right">1310</TD><TD style="TEXT-ALIGN: right">1360</TD><TD style="TEXT-ALIGN: right">1560</TD><TD style="TEXT-ALIGN: right">1860</TD><TD style="TEXT-ALIGN: right">1860</TD><TD style="TEXT-ALIGN: right">2400</TD><TD style="TEXT-ALIGN: right">1860</TD><TD style="TEXT-ALIGN: right">2400</TD><TD style="TEXT-ALIGN: right">2400</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD>Unique Height</TD><TD>List</TD><TD>Count</TD><TD style="TEXT-ALIGN: center">Width 1</TD><TD style="TEXT-ALIGN: center">Width 2</TD><TD style="TEXT-ALIGN: center">Width 3</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: right">7</TD><TD style="TEXT-ALIGN: right">610</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">690</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD></TD><TD style="TEXT-ALIGN: right">730</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">780</TD><TD style="TEXT-ALIGN: right">1010</TD><TD style="TEXT-ALIGN: right">1310</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD></TD><TD style="TEXT-ALIGN: right">1020</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">1010</TD><TD style="TEXT-ALIGN: right">1310</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD></TD><TD style="TEXT-ALIGN: right">1360</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">1360</TD><TD style="TEXT-ALIGN: right">1560</TD><TD style="TEXT-ALIGN: right">1860</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD></TD><TD style="TEXT-ALIGN: right">1670</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">1860</TD><TD style="TEXT-ALIGN: right">2400</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD></TD><TD style="TEXT-ALIGN: right">1970</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">1860</TD><TD style="TEXT-ALIGN: right">2400</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD></TD><TD style="TEXT-ALIGN: right">2400</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">2400</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>A5</TD><TD>{=SUM(IF(FREQUENCY(IF(B1:O1<>"",MATCH("~"&B1:O1,B1:O1&"",0)),COLUMN(B1:O1)-COLUMN(B1)+1),1))}</TD></TR><TR><TD>B5</TD><TD>{=IF(ROWS(B$5:B5)>$A$5,"",INDEX($B$1:$O$1,,MATCH(0,COUNTIF($B$1:$O$1,"<"&$B$1:$O$1)-SUM(COUNTIF($B$1:$O$1,"="&B$4:B4)),0)))}</TD></TR><TR><TD>C5</TD><TD>=IF(B5="","",COUNTIF($B$1:$O$1,B5))</TD></TR><TR><TD>D5</TD><TD>{=IF(COLUMNS($D5:D5)>$C5,"",INDEX($B$2:$O$2,,SMALL(IF($B$1:$O$1=$B5,COLUMN($B$1:$O$1)-COLUMN($B$1)+1),COLUMNS($D5:D5))))}</TD></TR><TR><TD>E5</TD><TD>{=IF(COLUMNS($D5:E5)>$C5,"",INDEX($B$2:$O$2,,SMALL(IF($B$1:$O$1=$B5,COLUMN($B$1:$O$1)-COLUMN($B$1)+1),COLUMNS($D5:E5))))}</TD></TR><TR><TD>F5</TD><TD>{=IF(COLUMNS($D5:F5)>$C5,"",INDEX($B$2:$O$2,,SMALL(IF($B$1:$O$1=$B5,COLUMN($B$1:$O$1)-COLUMN($B$1)+1),COLUMNS($D5:F5))))}</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!
</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

Try this...

A15 = drop down list 1

As the source for the dependent drop down list:

=OFFSET(B2,,MATCH(A15,B1:O1,0)-1,,COUNTIF(B1:O1,A15))

 

[How_Do_I]

That is sweet Biff, thanks for that...

There you go Simon, this is now what you need...

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Unit Height (mm)	610	730	730	730	1020	1020	1360	1360	1360	1670	1670	1970	1970	2400
2	Unit Width (mm)	690	780	1010	1310	1010	1310	1360	1560	1860	1860	2400	1860	2400	2400
3
4	Unique Height	List
5	7	610
6		730
7		1020
8		1360
9		1670
10		1970
11		2400
12
13
14
15	1360	1560
Sheet1

#VALUE!

 

[SimonHughes]

many thanks to you all, I really appreciate it, problem solved.

 

[T. Valko]

That is sweet Biff, thanks for that...

You're welcome!