Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Check Boxes & Data Validation
I have two check boxes (from forms toolbar), Two named ranges, and one cell
for a dropdown (Data Validation list). Is it possible to tie one of the check boxes to each of the named ranges and have the selected check box populate that named range on the data validation list. Mike Rogers |
#2
|
|||
|
|||
Hi!
You'd be better off using option buttons. Using checkboxes allows for more than one to be checked at a time. Only one option button can be selected at a time. I'll reply as though you are using option buttons. OK, so you have 2 option buttons and 2 named ranges: Range1 and Range2. Edit the text of the option buttons and insert the named ranges. Option button 1 will be for Range1 and option button 2 will be for Range2. Link the option buttons to a cell, say, A1. For the data validation..... Select List and enter this formula in the Source box: =CHOOSE(A1,Range1,Range2) When you select ob1 it will return 1 to cell A1. When you select ob2 it will return 2 to cell A1. If you do this you might get a message when you're setting the data validation to the effect: The source currently evaluates to an error. Do you want to continue? Just answer Yes. The reason you would get that message is because initially, neither button would be selected meaning that there is no source. Biff "Mike R" wrote in message ... I have two check boxes (from forms toolbar), Two named ranges, and one cell for a dropdown (Data Validation list). Is it possible to tie one of the check boxes to each of the named ranges and have the selected check box populate that named range on the data validation list. Mike Rogers |
#3
|
|||
|
|||
Hey Biff
Got the idea and it makes sense. A couple of questions, do I use the option buttons from control toool box or forms tool bar? I am assuming I will use control tool b yox because it has a properties button that I can use to edit. Which leads me to my next question. You said to edit the option button to insert a named range, how do I do that? I look at the information properties and it is pretty foreign to me. I can link the buttons to a cell but need direction on editing the text to insert named ranges. Thanks for the help Biff you have helped me many times before, so thanks again Mike Rogers "Biff" wrote: Hi! You'd be better off using option buttons. Using checkboxes allows for more than one to be checked at a time. Only one option button can be selected at a time. I'll reply as though you are using option buttons. OK, so you have 2 option buttons and 2 named ranges: Range1 and Range2. Edit the text of the option buttons and insert the named ranges. Option button 1 will be for Range1 and option button 2 will be for Range2. Link the option buttons to a cell, say, A1. For the data validation..... Select List and enter this formula in the Source box: =CHOOSE(A1,Range1,Range2) When you select ob1 it will return 1 to cell A1. When you select ob2 it will return 2 to cell A1. If you do this you might get a message when you're setting the data validation to the effect: The source currently evaluates to an error. Do you want to continue? Just answer Yes. The reason you would get that message is because initially, neither button would be selected meaning that there is no source. Biff "Mike R" wrote in message ... I have two check boxes (from forms toolbar), Two named ranges, and one cell for a dropdown (Data Validation list). Is it possible to tie one of the check boxes to each of the named ranges and have the selected check box populate that named range on the data validation list. Mike Rogers |
#4
|
|||
|
|||
Biff
Just kept "clicking" around and it was a "Duh" when I found edit text.... got it working!!!! Thanks again Mike Rogers "Biff" wrote: Hi! You'd be better off using option buttons. Using checkboxes allows for more than one to be checked at a time. Only one option button can be selected at a time. I'll reply as though you are using option buttons. OK, so you have 2 option buttons and 2 named ranges: Range1 and Range2. Edit the text of the option buttons and insert the named ranges. Option button 1 will be for Range1 and option button 2 will be for Range2. Link the option buttons to a cell, say, A1. For the data validation..... Select List and enter this formula in the Source box: =CHOOSE(A1,Range1,Range2) When you select ob1 it will return 1 to cell A1. When you select ob2 it will return 2 to cell A1. If you do this you might get a message when you're setting the data validation to the effect: The source currently evaluates to an error. Do you want to continue? Just answer Yes. The reason you would get that message is because initially, neither button would be selected meaning that there is no source. Biff "Mike R" wrote in message ... I have two check boxes (from forms toolbar), Two named ranges, and one cell for a dropdown (Data Validation list). Is it possible to tie one of the check boxes to each of the named ranges and have the selected check box populate that named range on the data validation list. Mike Rogers |
#5
|
|||
|
|||
We all have those "duh" moments!
Glad you got it working. Biff "Mike R" wrote in message ... Biff Just kept "clicking" around and it was a "Duh" when I found edit text.... got it working!!!! Thanks again Mike Rogers "Biff" wrote: Hi! You'd be better off using option buttons. Using checkboxes allows for more than one to be checked at a time. Only one option button can be selected at a time. I'll reply as though you are using option buttons. OK, so you have 2 option buttons and 2 named ranges: Range1 and Range2. Edit the text of the option buttons and insert the named ranges. Option button 1 will be for Range1 and option button 2 will be for Range2. Link the option buttons to a cell, say, A1. For the data validation..... Select List and enter this formula in the Source box: =CHOOSE(A1,Range1,Range2) When you select ob1 it will return 1 to cell A1. When you select ob2 it will return 2 to cell A1. If you do this you might get a message when you're setting the data validation to the effect: The source currently evaluates to an error. Do you want to continue? Just answer Yes. The reason you would get that message is because initially, neither button would be selected meaning that there is no source. Biff "Mike R" wrote in message ... I have two check boxes (from forms toolbar), Two named ranges, and one cell for a dropdown (Data Validation list). Is it possible to tie one of the check boxes to each of the named ranges and have the selected check box populate that named range on the data validation list. Mike Rogers |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
data validation | Excel Worksheet Functions | |||
Effect of Conditional Formatting, Data Validation | Excel Discussion (Misc queries) | |||
Data Validation Window? | Excel Discussion (Misc queries) | |||
How can I check if data in an external data range is changed afte. | Excel Worksheet Functions | |||
Excel2K: Is it possible to use dynamic named ranges in custom data validation formula? | Excel Discussion (Misc queries) |