Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a few, non-adjacent cells on one sheet which I want to use as a Source for Data Validation in a cell on another sheet. However, it seems Data Validation doesn't natively permit this.

So, I tried to do a work-around that involved putting formulas into some adjacent cells on another sheet which would reference the values I wanted from the first sheet. Then, I named the second range and referenced that name in the Data Validation Source field.

Now I have a problem where, if one of the cells from the first sheet is blank, a cell on the second sheet that references a blank on the first will show 0 instead of being blank. I tried to work around this by doing something like =IF(Sheet1!A1="","",Sheet1!A1). However, this doesn't actually make the formula result the same as a blank cell.

This all mostly becomes problematic when I want my Data Validation to include an in-cell drop-down. My choices here appear to be either to have 0 or a blank line in the drop-down wherever there are blanks in the Source data. If the blanks were true blanks, this wouldn't happen.

So, is there any way to work around this?



A1= Value1
A5= Value2
A9= Value3
A13= Value4
A17= (Cell is blank)
A21= (Cell is blank)


A1 =Sheet1!A1 (returns Value1)
A2 =Sheet1!A5 (returns Value2)
A3 =Sheet1!A9 (returns Value3)
A4 =Sheet1!A13 (returns Value4)
A5 =Sheet1!A17 (returns 0)
A6 =IF(Sheet1!A21="","",Sheet1!A21) (appears blank)

Sheet2!A1:A6 is named Validation. At Sheet3!A1, Data Validation is applied with source =Validation and an in-cell drop-down. The in-cell drop-down shows:


(Blank cell included)

Under these conditions, I'm looking for a configuration that will result in a drop-down in Sheet3!A1 that only shows Sheet2!A1:A4 while also keeping Sheet2!A5:A6 available should they ever be populated. Alternately, the drop-down in Sheet3!A1 should show only Sheet1!A1,Sheet1!A5,Sheet1!A9,Sheet1!A13 while also keeping Sheet1!A17,Sheet1!A21 available should they ever be populated.

It seems I need a way to either:

  • Directly address the non-adjacent cells in Sheet1, in my Data Validation Source
  • Get the cells in the Validation range in Sheet2 to actually return blank cells when their targets in Sheet1 are blank.
share|improve this question
This will require at least two methods, pending on how abstract you want to process the information. When i get to work tomorrow ill give you some starter methods to get you going down the correct road. In the mean time read up on this posting on StackOverflow: DataValidation filters Table – GoldBishop Oct 16 '12 at 2:37
@GoldBishop Looking forward to it. Meanwhile, I've worked around the problem for this particular case. Still need some help over here though. – Iszi Oct 16 '12 at 3:11

Using the method described in your original post How do i make a Data Validation drop-down exclude blanks.

Couple this with these methods:

Public Sub ClearDataValidation(destrng As Range)
End Sub

Public Sub LoadDataValidation(srcrng As Range, destrng As Range)
   'Verify a 1x1 sized Range was passed
   If destrng.Rows.Count <> 1 Or destrng.Columns.Count <> 1 Then
      InvalidValue destrng.Worksheet, "LoadDataValidation", _
            "Range: " & & " was passed to method. This method expects a " & vbCrLf & vbCrLf & _
            " 1 Row x 1 Column Range to be passed.  Anything outside of the 1x1 " & vbCrLf & vbCrLf & _
            "size will result in invalid conditions"
      Exit Sub
   End If

   With Range(destrng.Address).Validation
      .Add xlValidateList, xlValidAlertStop, xlBetween, DistinctValues(srcrng)
   End With
End Sub


LoadDataValidation Range("Table1[column1]"), Range("destinationCell")
share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .