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? |
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 |
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 |
All times are GMT +1. The time now is 08:37 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com