I want to have the data validation of a cell (B9) set to a list of values but only to include the cells B7,B11,B15,B19 from a different sheet. I do not want the gap values to appear.

Note also, the cells B7,B11,B15,B19 contain formulas hence the list should contain the up to date fromula values.

Any Ideas?


Say we want to use the values from a worksheetsheet called other.

Pick a cell, say D1,and enter:


into it and the cells below it. Then apply DV using the little list in D1 through D4:

enter image description here

This method allows you to pick values from various disjoint sources.

  • Thank you. Will this list automatically update when the values of B7,B11,B15,B19 change? – Emily G Jun 29 '17 at 10:06
  • @EmilyG ......yes............... – Gary's Student Jun 29 '17 at 11:43
  • thank you. could you consider my next question for me if you have time: superuser.com/questions/1223996/… – Emily G Jun 29 '17 at 11:53
  • @EmilyG I will look later – Gary's Student Jun 29 '17 at 11:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.