Hi,

A worksheet contains data as follows:

Column A ColumnnB
Country1 $B$24:$B$30
Country2 $B$9:$B$18
Country3 $B$42:$B$51
and so on

Column B has the range in another sheet where the list of States/
counties in the corresponding country are stored

When the user clicks in the corresponding cell in Column C,a dropdown
list should display the names in the range in column B and validate
that only one of those values is input.

How can this be achieved through VBA?

Thanks in advance for the Help.

Regards,
Raj

Hi Raj,

Assuming your table begins in cell A1 and your list ranges are on Sheet1
you can create a data validation list in C1 as follows:

Choose Data Validation List from the Excel menu.
In the Source box enter the formula =INDIRECT("Sheet1!" & B1)
Click OK

You can then copy this down for as many cells in column C as you need it.
Change "Sheet1" to the name of your list worksheet first and be sure to
surround the worksheet name in single quotes if it has any spaces or special
characters.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

"Raj" wrote in message
...
Hi,

A worksheet contains data as follows:

Column A ColumnnB
Country1 $B$24:$B$30
Country2 $B$9:$B$18
Country3 $B$42:$B$51
and so on

Column B has the range in another sheet where the list of States/
counties in the corresponding country are stored

When the user clicks in the corresponding cell in Column C,a dropdown
list should display the names in the range in column B and validate
that only one of those values is input.

How can this be achieved through VBA?

Thanks in advance for the Help.

Regards,
Raj

Hi Bob,

Thanks,Bob. It worked.

Regards,
Raj

On May 21, 11:11*pm, "Rob Bovey" wrote:
Hi Raj,

* * Assuming your table begins in cell A1 and your list ranges are on Sheet1
you can create a data validation list in C1 as follows:

Choose Data Validation List from the Excel menu.
In the Source box enter the formula =INDIRECT("Sheet1!" & B1)
Click OK

You can then copy this down for as many cells in column C as you need it.
Change "Sheet1" to the name of your list worksheet first and be sure to
surround the worksheet name in single quotes if it has any spaces or special
characters.

--
Rob Bovey, Excel MVP
Application Professionalshttp://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Developmenthttp://www.appspro.com/Books/Books.htm

"Raj" wrote in message

...

Hi,

A worksheet contains data as follows:

Column A * ColumnnB
Country1 * * * $B$24:$B$30
Country2 * * * $B$9:$B$18
Country3 * * * $B$42:$B$51
and so on

Column B has the range in another sheet where the list of States/
counties in the corresponding country are stored

When the user clicks in the corresponding cell in Column C,a dropdown
list should display the names in the range in column B and validate
that only one of those values is input.

How can this be achieved through VBA?

Thanks in advance for the Help.

Regards,
Raj