Vlookup function to generate a dropdown list?

[blkmagik]

Is it possible to use the Vlookup function to generate a dropdown list. For example, lets say the database had two columns. The first column was based on the name of various States and the second column had multiple rows of each city within that region. Suppose I wanted the drop down list to show only the names of the cities within California based on a Vlookup formula looking at another cell that had the name of the State to look for. Is this possible?
I know I can filter the data but what I am attempting to do is to grab data from the list of results found in the Table and insert in another workbook using the vlookup formula
Table Example:
<TABLE class=MsoNormalTable style="MARGIN: auto auto auto 4.65pt; WIDTH: 193.3pt; BORDER-COLLAPSE: collapse; mso-yfti-tbllook: 1184; mso-padding-alt: 0in 5.4pt 0in 5.4pt" cellSpacing=0 cellPadding=0 width=258 border=0><TBODY><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 48pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=64></TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 61.55pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=82>A<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 83.75pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=112>B<o></o>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 1"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 48pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=64>

1<o></o>​

</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 61.55pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=82>CALIFORNIA<o></o>
</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 83.75pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=112>LOS ANGELES<o></o>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 2"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 48pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=64>

2<o></o>​

</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 61.55pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=82>CALIFORNIA<o></o>
</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 83.75pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=112>SACRAMENTO<o></o>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 3"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 48pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=64>

3<o></o>​

</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 61.55pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=82>CALIFORNIA<o></o>
</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 83.75pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=112>SAN FRANCISCO<o></o>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 4"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 48pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=64>

4<o></o>​

</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 61.55pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=82>FLORIDA<o></o>
</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 83.75pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=112>TAMPA<o></o>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 5; mso-yfti-lastrow: yes"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 48pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=64>

5<o></o>​

</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 61.55pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=82>FLORIDA<o></o>
</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 83.75pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=112>ORLANDO<o></o>
</TD></TR></TBODY></TABLE><o> </o>

 

[Trevor G]

What about something like a drop down (validation) which then filters to another drop down. Sample sheet which could be towns in Counties.

Lists

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial Narrow,Arial; FONT-SIZE: 12pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 76px"><COL style="WIDTH: 64px"><COL style="WIDTH: 110px"><COL style="WIDTH: 110px"></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></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="BACKGROUND-COLOR: #ccffcc">Fruit</TD><TD style="BACKGROUND-COLOR: #ffff99">Apples</TD><TD style="BACKGROUND-COLOR: #ffcc99">Cabbage</TD><TD style="BACKGROUND-COLOR: #ccffff">Bread</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="BACKGROUND-COLOR: #ccffcc">Other Stuff</TD><TD style="BACKGROUND-COLOR: #ffff99">Oranges</TD><TD style="BACKGROUND-COLOR: #ffcc99">Lettuce</TD><TD style="BACKGROUND-COLOR: #ccffff">Meat</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="BACKGROUND-COLOR: #ccffcc">Vegetables</TD><TD style="BACKGROUND-COLOR: #ffff99">Lemons</TD><TD style="BACKGROUND-COLOR: #ffcc99">Carrots</TD><TD style="BACKGROUND-COLOR: #ccffff">Chocolate</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD></TD><TD style="BACKGROUND-COLOR: #ffff99">Grapes</TD><TD style="BACKGROUND-COLOR: #ffcc99">Beets</TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #ffcc99">Brussels Sprouts</TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #ffcc99">Celery</TD><TD></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

Second sheet with validations and the formula to work in the validation

Data Entry

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial Narrow,Arial; FONT-SIZE: 12pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 94px"><COL style="WIDTH: 116px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Category</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Item</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>Other Stuff</TD><TD style="TEXT-ALIGN: left">Meat</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>Vegetables</TD><TD style="TEXT-ALIGN: left">Lettuce</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>Other Stuff</TD><TD> </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

On the Lists sheet, Column A contains a dynamic range named Category, that is the source for the data validation in the Category column here.

On the Lists sheet, Cell B1 is named Fruit, Cell C1 is named Vegetables, Cell D1 is named OtherStuff

On the Lists sheet, Column B is named FruitCol, Column C is named VegetablesCol, Column D is named OtherStuffCol

These names are used in the Data Validation formula in the Item column:

=OFFSET(INDIRECT(SUBSTITUTE($F2," ","")),0,0,COUNTA(INDIRECT(SUBSTITUTE($F2," ","")&"Col")),1)

The SUBSTITUTE function is used to remove the spaces in two word items, such as Other Stuff,
because range names cannot contain a space character.

To see the names, choose The Formula Tab>Name Manager​