Excel – How to use non-adjacent cells on another sheet for a Data Validation drop-down, and only show non-blank values

data validationmicrosoft-excel-2010

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.

Best Answer

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")

Related Solutions

Excel – How to make a Data Validation drop-down exclude blanks

Here is a method that i use in a current project.

'@ws - Worksheet object
'@col - String value
'Notes:
'  - @ws should be the worksheet that contains the Range @col
'  - @col should be a Column/Row based Range, should be only 1 Column or Row
Function UniqueValues(ws As Worksheet, col As String) As Variant

   Dim rng As Range: Set rng = ws.Range(col)
   Dim dict As New Scripting.Dictionary

   If Not (rng Is Nothing) Then
      Dim cell As Range, val As String

      For Each cell In rng.Cells
         val = CStr(cell.Value)

         If InStr(1, val, ",") > 0 Then
            val = Replace(val, ",", Chr(130))
         End If

         If Not dict.Exists(val) Then
            dict.Add val, val
         End If

      Next cell
   End If

   'Return value as Variant Array
   UniqueValues = dict.Items
End Function

You will probably want to put in a check for a Value val > 0 ,val & "" <> "" or IsNullOrEmpty(val). IsNullOrEmpty is a UDF that i created since VBA does not contain a String method to do this as one.

I have posted several code-snippets at Microsoft Wikia site. They are very generic and can be used for just about any purpose, as they are specified.

Excel – IF/THEN drop down list

Like you said you can use another cell M1 for example :
=If(M173=True,"Fine","No")
I used Fine just in case you need Yes in another place.
Select the options of your Dropdown List and define Name Fine
In K72 create Data Validation, choose List
In Source write:
=Indirect(M1)
In that case when M1 is different from Fine the dropdown is empty, when you have Fine in M1 K72 will show the values that you want to choose from the list

Update for Drop Down List become blank
Define name a blank cell No make it in a special place so you don't use it for any value (near the Fine list or yes list)
Keep the data validation Source like before =Indirect(M1)
If M1 = "No" the List will become blank (you have to choose the blank to clear the cell if it has any value)
If M1="Yes" or "Fine" whatever you choose the List will have the options you need
Just use normal word for Define Name do not use TRUE, False...

To Clear it automatically you will need VBA

Best Answer

Usage:

Related Solutions

Excel – How to make a Data Validation drop-down exclude blanks

Excel – IF/THEN drop down list

Related Question