How can I use non-adjacent cells on another sheet for a Data Validation drop-down, and only show non-blank values?

Question

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?

---

Example:

Sheet1

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

Sheet2

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:

Value1
Value2
Value3
Value4
0

(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
  OR
• Get the cells in the Validation range in Sheet2 to actually return blank cells when their targets in Sheet1 are blank.

Answer 1

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)
   destrng.Validation.Delete
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: " & destrng.name & " 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
      .Delete
      .Add xlValidateList, xlValidAlertStop, xlBetween, DistinctValues(srcrng)
   End With
End Sub

Usage:

LoadDataValidation Range("Table1[column1]"), Range("destinationCell")