I am using a validation option list in a column (2 different listing
options). The rows need to be sorted but when that happens, the correct list
options doesn't match anymore. How can I use a list selection in a cell and
be able to sort the spreadsheet as well? Thank you

If I understand correctly, this should help...

Put the lists for your data validations on another sheet. For each list,
select the cells to include and assign a range name to that selection. To
create the cell validation dropdowns, select List in the Data Validation
dialog and enter =YourRangeName in the Source box. For example, if the
validation is for the months of the year, enter them on another sheet, select
all 12 cells, and in the Name Box assign the name Months. When adding data
validation for months on the main sheet, select List in the Allow options and
enter =Months in the Source box. Wherever the cell is moved, it will always
use the range Months for its validation list.

Hope this helps,

Hutch

"hmaze" wrote:

I am using a validation option list in a column (2 different listing
options). The rows need to be sorted but when that happens, the correct list
options doesn't match anymore. How can I use a list selection in a cell and
be able to sort the spreadsheet as well? Thank you

Hi Tom - It did not work for me. Example: I use rows 1-10 for one list (A)
and 11-20 for the other (B) - list is on a different sheet. When I sort the
spreadsheet, the list options are still right 1-10 and 11-20, however row 1
got resorted to row 15 and is giving me option B now, while it should give
list option A.

"Tom Hutchins" wrote:

If I understand correctly, this should help...

Put the lists for your data validations on another sheet. For each list,
select the cells to include and assign a range name to that selection. To
create the cell validation dropdowns, select List in the Data Validation
dialog and enter =YourRangeName in the Source box. For example, if the
validation is for the months of the year, enter them on another sheet, select
all 12 cells, and in the Name Box assign the name Months. When adding data
validation for months on the main sheet, select List in the Allow options and
enter =Months in the Source box. Wherever the cell is moved, it will always
use the range Months for its validation list.

Hope this helps,

Hutch

"hmaze" wrote:

I am using a validation option list in a column (2 different listing
options). The rows need to be sorted but when that happens, the correct list
options doesn't match anymore. How can I use a list selection in a cell and
be able to sort the spreadsheet as well? Thank you

That's why I wrote "If I understand correctly..." You have a different
problem than I thought. I am unaware of a work-around for sorting validation.
Cell borders and conditionally formatted colors also do not sort with the
data. Hopefully, one of the MVP's knows a way, and we can both learn.

Sorry I couldn't be of more help,

Hutch

"hmaze" wrote:

Hi Tom - It did not work for me. Example: I use rows 1-10 for one list (A)
and 11-20 for the other (B) - list is on a different sheet. When I sort the
spreadsheet, the list options are still right 1-10 and 11-20, however row 1
got resorted to row 15 and is giving me option B now, while it should give
list option A.

"Tom Hutchins" wrote:

If I understand correctly, this should help...

Put the lists for your data validations on another sheet. For each list,
select the cells to include and assign a range name to that selection. To
create the cell validation dropdowns, select List in the Data Validation
dialog and enter =YourRangeName in the Source box. For example, if the
validation is for the months of the year, enter them on another sheet, select
all 12 cells, and in the Name Box assign the name Months. When adding data
validation for months on the main sheet, select List in the Allow options and
enter =Months in the Source box. Wherever the cell is moved, it will always
use the range Months for its validation list.

Hope this helps,

Hutch

"hmaze" wrote:

I am using a validation option list in a column (2 different listing
options). The rows need to be sorted but when that happens, the correct list
options doesn't match anymore. How can I use a list selection in a cell and
be able to sort the spreadsheet as well? Thank you

Tom - NP, thank you for looking at my issue

"Tom Hutchins" wrote:

That's why I wrote "If I understand correctly..." You have a different
problem than I thought. I am unaware of a work-around for sorting validation.
Cell borders and conditionally formatted colors also do not sort with the
data. Hopefully, one of the MVP's knows a way, and we can both learn.

Sorry I couldn't be of more help,

Hutch

"hmaze" wrote:

Hi Tom - It did not work for me. Example: I use rows 1-10 for one list (A)
and 11-20 for the other (B) - list is on a different sheet. When I sort the
spreadsheet, the list options are still right 1-10 and 11-20, however row 1
got resorted to row 15 and is giving me option B now, while it should give
list option A.

"Tom Hutchins" wrote:

If I understand correctly, this should help...

Put the lists for your data validations on another sheet. For each list,
select the cells to include and assign a range name to that selection. To
create the cell validation dropdowns, select List in the Data Validation
dialog and enter =YourRangeName in the Source box. For example, if the
validation is for the months of the year, enter them on another sheet, select
all 12 cells, and in the Name Box assign the name Months. When adding data
validation for months on the main sheet, select List in the Allow options and
enter =Months in the Source box. Wherever the cell is moved, it will always
use the range Months for its validation list.

Hope this helps,

Hutch

"hmaze" wrote:

I am using a validation option list in a column (2 different listing
options). The rows need to be sorted but when that happens, the correct list
options doesn't match anymore. How can I use a list selection in a cell and
be able to sort the spreadsheet as well? Thank you