Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation linked to cell Formula
I have a spreadsheet where i have created a number of drop down lists using
data validation. These lists refer to cells which in turn refer to other cells. I some of the Drop downs I created the cells update due to changes in data elsewhere in the sheet, however in others they don't., I wouls like them all to update. I cant see what I doing wrong any assistance great fully recieved. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation linked to cell Formula
If you give an example of the layout, and the formulas that you're using
in the Data Validation cells, someone may be able to help. Buzz wrote: I have a spreadsheet where i have created a number of drop down lists using data validation. These lists refer to cells which in turn refer to other cells. I some of the Drop downs I created the cells update due to changes in data elsewhere in the sheet, however in others they don't., I wouls like them all to update. I cant see what I doing wrong any assistance great fully recieved. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation linked to cell Formula
Sorry new to this discussion group thing , I did not want to overburden with
detail intially, but below is more detail cell A1 has a drop down which references cells b1:b3, cell b1 contains a formula "=c1", in some of the drop downs when c1 changes the drop down menu automatically updates, and is some its only when you select the drop down menu do you see the correct values in the drop down menu. I would like all the drop downs to automaticaaly update. In the drop downs which work in a1 you can see the formula "=c1", in the others you only see the value of C1 any assistance greatly appreciated. "Debra Dalgleish" wrote: If you give an example of the layout, and the formulas that you're using in the Data Validation cells, someone may be able to help. Buzz wrote: I have a spreadsheet where i have created a number of drop down lists using data validation. These lists refer to cells which in turn refer to other cells. I some of the Drop downs I created the cells update due to changes in data elsewhere in the sheet, however in others they don't., I wouls like them all to update. I cant see what I doing wrong any assistance great fully recieved. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation linked to cell Formula
I don't think that there's anything inside Data|Validation that will change the
value of a cell when the list changes. In fact, lots of lists can are based on dynamic ranges--that grow and contract when the number of items in the list are added/deleted. It would be pretty difficult to make sure you cover every contigency. And even more...You can use data|validation as a warning tool. You can let the user type something in that cell with data|validation that doesn't appear on the list. It could be quite a pain to come up with something that would work all the time (although, maybe under specific circumstancs, you could have something that worked fine). But it might be easier to drop the data|validation and use a combobox from the Control toolbox toolbar. Put it over cell A1. While in design mode (another icon on that control toolbox toolbar), right click on it and choose properties. Set the style to fmStyleDropDownList (to only allow choosing from the dropdown) Assign the linked cell to A1 (so you can use it other formulas???) Assign the ListFillRange to B1:B3. Now if the combobox has the value equal to what's in B1 (which points at C1), and C1 changes, the value in the combobox (and A1) will change. This seems a lot simpler than struggling with Data|Validation--well, to me anyway. Buzz wrote: Sorry new to this discussion group thing , I did not want to overburden with detail intially, but below is more detail cell A1 has a drop down which references cells b1:b3, cell b1 contains a formula "=c1", in some of the drop downs when c1 changes the drop down menu automatically updates, and is some its only when you select the drop down menu do you see the correct values in the drop down menu. I would like all the drop downs to automaticaaly update. In the drop downs which work in a1 you can see the formula "=c1", in the others you only see the value of C1 any assistance greatly appreciated. "Debra Dalgleish" wrote: If you give an example of the layout, and the formulas that you're using in the Data Validation cells, someone may be able to help. Buzz wrote: I have a spreadsheet where i have created a number of drop down lists using data validation. These lists refer to cells which in turn refer to other cells. I some of the Drop downs I created the cells update due to changes in data elsewhere in the sheet, however in others they don't., I wouls like them all to update. I cant see what I doing wrong any assistance great fully recieved. -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation linked to cell Formula
Dave,
Thank you this works, the thing with excel there are so many different ways to skin the proverbial cat. Thanks again. "Dave Peterson" wrote: I don't think that there's anything inside Data|Validation that will change the value of a cell when the list changes. In fact, lots of lists can are based on dynamic ranges--that grow and contract when the number of items in the list are added/deleted. It would be pretty difficult to make sure you cover every contigency. And even more...You can use data|validation as a warning tool. You can let the user type something in that cell with data|validation that doesn't appear on the list. It could be quite a pain to come up with something that would work all the time (although, maybe under specific circumstancs, you could have something that worked fine). But it might be easier to drop the data|validation and use a combobox from the Control toolbox toolbar. Put it over cell A1. While in design mode (another icon on that control toolbox toolbar), right click on it and choose properties. Set the style to fmStyleDropDownList (to only allow choosing from the dropdown) Assign the linked cell to A1 (so you can use it other formulas???) Assign the ListFillRange to B1:B3. Now if the combobox has the value equal to what's in B1 (which points at C1), and C1 changes, the value in the combobox (and A1) will change. This seems a lot simpler than struggling with Data|Validation--well, to me anyway. Buzz wrote: Sorry new to this discussion group thing , I did not want to overburden with detail intially, but below is more detail cell A1 has a drop down which references cells b1:b3, cell b1 contains a formula "=c1", in some of the drop downs when c1 changes the drop down menu automatically updates, and is some its only when you select the drop down menu do you see the correct values in the drop down menu. I would like all the drop downs to automaticaaly update. In the drop downs which work in a1 you can see the formula "=c1", in the others you only see the value of C1 any assistance greatly appreciated. "Debra Dalgleish" wrote: If you give an example of the layout, and the formulas that you're using in the Data Validation cells, someone may be able to help. Buzz wrote: I have a spreadsheet where i have created a number of drop down lists using data validation. These lists refer to cells which in turn refer to other cells. I some of the Drop downs I created the cells update due to changes in data elsewhere in the sheet, however in others they don't., I wouls like them all to update. I cant see what I doing wrong any assistance great fully recieved. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to sort but keep linked formulas? | Excel Discussion (Misc queries) | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Formula and Data Entry in a Single Cell | Excel Discussion (Misc queries) | |||
Data validation, cell protection or other method? | Excel Discussion (Misc queries) | |||
Data Validation Formula Help | Excel Worksheet Functions |