Hi Everyone,
I have a spreadsheet I'm developing that is using data validation > lists. The lists below 'Product' are all dependent upon the contents of the fields above (with bold outlines). I am using the 'INDIRECT' function and named ranges to 'filter' (for lack of a better word) what appears in each pick list so it depends on the contents of the cells above (first pic on the left). For example, if 'EL' and 'MD' are chosen, then the only Utility that should appear in the list should be BG&E. If 'EL' & 'PA' are chosen, then 'DUQ, Met-Ed, PECO, PPL' should appear as options in the pick list (pic on the right) because the formula for the list in cell A8, in the first attachment is '=INDIRECT(""&$A$5&"_"&$A$2)' and refers to the named range in the order of 'State' then 'Product'. I hope this explanation makes sense.
My question is how do i get the picklists to automatically refresh when the contents of a cell above changes. So if i first choose 'BGE' when 'EL' and 'MD' are chosen above, and then pick 'BG&E' for the utility (it is the only one that is listed based on the criteria above) but then I want to change the state to 'PA', it still leaves in 'BG&E' in the Utility cell even though it should no longer be listed. You must go back to the Utility picklist and re-choose a PA Utility.
It would be ideal if 'BG&E' automatically disappeared if the cell above changes to another state, like 'PA' for example.
If this doesn't make sense I can try to post the file. Thanks in advance for any and all help! You guys are great!
Bookmarks