Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.
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

1 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")
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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