Forum Discussion
Data validation for multiple look ups
Hi I'm not sure if what I am trying to do is possible, but I'm trying to create a data validation list from 2 cells that are also data validation lists. I need to have this as a list that can be...
It would help if you can provide a workbook. If you can't attach it here you can post it on sharepoint or onedrive or something similar and share a link. make sure it doesn't have any kind of personal/confidential information.
I also wonder if your use of "data validation list" is the same as what I am thinking. To me, a data validation list is used in the Data Validation tool to provide a drop down of valid entries. i.e. if cell D1 has data validation turned on and the data validation is set to allow the list A1:A100 then when you click the down arrow on D1 you will see that list BUT you can only pick 1 value from that list. Hence when you say "from 2 cells that are also data validation lists" I question if you mean 2 cells that are restricted based on data validation lists or 2 actual lists (i.e. an array formula in that cell that spills an array of values). I suspect it is the later and you are trying to use that 'spill' data in the 'traditional' way like the formula is in G1 and spilling into G1:G100 and you are doing a fill down in H1:H xxx to use the unknown # of cells in column G. The answer to this problem is to use ANOTHER array formula in H1 and get the data from column G using G1# (the # symbol meaning the entire spill range).
I also wonder if your use of "data validation list" is the same as what I am thinking. To me, a data validation list is used in the Data Validation tool to provide a drop down of valid entries. i.e. if cell D1 has data validation turned on and the data validation is set to allow the list A1:A100 then when you click the down arrow on D1 you will see that list BUT you can only pick 1 value from that list. Hence when you say "from 2 cells that are also data validation lists" I question if you mean 2 cells that are restricted based on data validation lists or 2 actual lists (i.e. an array formula in that cell that spills an array of values). I suspect it is the later and you are trying to use that 'spill' data in the 'traditional' way like the formula is in G1 and spilling into G1:G100 and you are doing a fill down in H1:H xxx to use the unknown # of cells in column G. The answer to this problem is to use ANOTHER array formula in H1 and get the data from column G using G1# (the # symbol meaning the entire spill range).