Как можно использовать несмежные ячейки на другом листе для раскрывающегося списка «Проверка данных» и отображать только непустые значения?

Question

У меня есть несколько несмежных ячеек на одном листе, которые я хочу использовать в качестве источника для проверки данных в ячейке на другом листе. Тем не менее, похоже, что Data Validation изначально не допускает этого.

Итак, я попытался обойти это, включив размещение формул в несколько смежных ячеек на другом листе, которые будут ссылаться на значения, которые я хотел получить с первого листа. Затем я назвал второй диапазон и сослался на это имя в поле Источник проверки данных.

Теперь у меня есть проблема, когда, если одна из ячеек на первом листе пуста, ячейка на втором листе, которая ссылается на пробел на первом, будет показывать 0 вместо того, чтобы быть пустой. Я пытался обойти это, делая что-то вроде =IF(Sheet1!A1="","",Sheet1!A1) . Однако, это не реально сделать формулу результат такой же , как пустую ячейку.

Все это в основном становится проблематичным, когда я хочу, чтобы моя проверка данных включала раскрывающийся список в ячейке. Здесь я выбираю выбор из 0 или пустой строки в раскрывающемся списке, где есть пробелы в исходных данных. Если бы заготовки были настоящими заготовками, этого бы не случилось.

Итак, есть ли способ обойти это?

---

Пример:

Лист1

A1 = Value1
A5 = Value2
A9 = Value3
A13 = Value4
A17 = (клетка пуста)
A21 = (клетка пуста)

Sheet2

A1 =Sheet1!A1 (возвращает Value1
A2 =Sheet1!A5 (возвращает Value2
A3 =Sheet1!A9 (возвращает Value3)
А4 =Sheet1!A13 (возвращает Value4)
A5 =Sheet1!A17 (возвращает 0)
A6 =IF(Sheet1!A21="","",Sheet1!A21) (отображается пустым)

Sheet2!A1:A6 называется Validation . На Sheet3!A1 , проверка данных применяется с источником =Validation и раскрывающийся список в ячейке. Раскрывающийся список внутри ячейки показывает:

Значение1
Value2
Value3
Value4
0

(Пустая ячейка включена)

В этих условиях я ищу конфигурацию, которая приведет к раскрытию в Sheet3!A1, который показывает только Sheet2!A1:A4 , сохраняя при этом Sheet2!A5:A6 доступны, если они когда-либо будут заселены. Поочередно выпадающий в Sheet3!А1 должен показывать только Sheet1!A1, Лист1!A5, Лист1!A9, Лист1!A13 , сохраняя при этом Sheet1!A17, Лист1!A21 доступны, если они когда-либо будут заселены.

Кажется, мне нужен способ либо:

• Непосредственно обратиться к несмежным ячейкам в Sheet1 , в моем источнике проверки данных
  ИЛИ ЖЕ
• Получите ячейки в диапазоне Validation на Sheet2 чтобы они фактически возвращали пустые ячейки, когда их цели на Sheet1 1 пусты.

Answer 1

Используя метод, описанный в исходной публикации. Как сделать, чтобы раскрывающийся список «Проверка данных» исключал пробелы.

Соедините это с этими методами:

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

Использование:

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