Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Validation Filtering
I am having difficulty combining dynamic ranges together with the
indirect function in data validation. I have a number of dynamic ranges that are created using the offset formula. I then have a worksheet with data validation. The data validation limits the choices a user can select from by converting a previous election into a range. e.g I have a dynamic range called "Collectables", the user first selects "Collectables" from a drop down list in cell A4, they then move onto another dropdown list in cell B4, the data validation is set to =INDIRECT(A4). This should allow the users to selct an item that is within the "Collectables" range. However it DOSN'T. If I make the range non dynamic i.e. Make it a fixed range it works fine. If I leave the dynamic range and change the data validation to =Collectables it works fine. It just won't work with both Dynamic ranges and the INDIRECT function. Is this a bug with Excel or is there a reason for it not working? Is there a way around it? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Validation Filtering
I think that this is just the way excel works.
If that dynamic range doesn't change very often (maybe just by the developer), maybe you could use the workbook_Open event to assign the name. If it changes by user changes, maybe you could use a worksheet_change event. John wrote: I am having difficulty combining dynamic ranges together with the indirect function in data validation. I have a number of dynamic ranges that are created using the offset formula. I then have a worksheet with data validation. The data validation limits the choices a user can select from by converting a previous election into a range. e.g I have a dynamic range called "Collectables", the user first selects "Collectables" from a drop down list in cell A4, they then move onto another dropdown list in cell B4, the data validation is set to =INDIRECT(A4). This should allow the users to selct an item that is within the "Collectables" range. However it DOSN'T. If I make the range non dynamic i.e. Make it a fixed range it works fine. If I leave the dynamic range and change the data validation to =Collectables it works fine. It just won't work with both Dynamic ranges and the INDIRECT function. Is this a bug with Excel or is there a reason for it not working? Is there a way around it? -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Validation Filtering
Also, there's a workaround explained he
http://www.contextures.com/xlDataVal02.html#Dynamic John wrote: I am having difficulty combining dynamic ranges together with the indirect function in data validation. I have a number of dynamic ranges that are created using the offset formula. I then have a worksheet with data validation. The data validation limits the choices a user can select from by converting a previous election into a range. e.g I have a dynamic range called "Collectables", the user first selects "Collectables" from a drop down list in cell A4, they then move onto another dropdown list in cell B4, the data validation is set to =INDIRECT(A4). This should allow the users to selct an item that is within the "Collectables" range. However it DOSN'T. If I make the range non dynamic i.e. Make it a fixed range it works fine. If I leave the dynamic range and change the data validation to =Collectables it works fine. It just won't work with both Dynamic ranges and the INDIRECT function. Is this a bug with Excel or is there a reason for it not working? Is there a way around it? -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Validation and Filtering | Excel Discussion (Misc queries) | |||
Data validation with hyperlinks | Excel Worksheet Functions | |||
Filtering and Data Validation | Excel Discussion (Misc queries) | |||
named range, data validation: list non-selected items, and new added items | Excel Discussion (Misc queries) | |||
Data Validation Window? | Excel Discussion (Misc queries) |