EXCEL入力規則のリスト表示から空白行を表示させない方法

EXCEL2013で質問です。
入力規則のリストで、他のシートにある連続入力されていない列の値を空白行を除いてリスト表示させたいです。
また、マクロやワークセルは使用しない考えています。
値が連続で最後の値以降の空白行を除くのであればOFFSETとCOUNTAの組み合わせでできるし、
ワークセルを使用するのであればこちらで実現できました。
http://excel.syogyoumujou.com/function/list.html
イメージ的には添付ファイルのような感じになります。
実現方法をご存知の方がいらっしゃいましたらご教授いただけると幸いです。

No.2 回答者： tom04 回答日時： 2015/09/15 09:59

No.1です。

質問文に
＞また、マクロやワークセルは使用しない考えています。
とありますが
入力規則のリスト設定を行う場合、ある範囲を指定するのが一般的だと思います。
（もしくは「元の値」の欄に直接リスト候補を入力する）

また、No.1のお礼欄に
＞可能であればこの作業列を使わずに実現したいのですが。
とありますので、どうしても作業列なしで！
というコトがご希望のようですので、お望みでないVBAであれば可能です。

とりあえず同一SheetのG列にリスト設定しています。

画面左下の操作したいSheet見出し上で右クリック　→　コードの表示　→　VBE画面のカーソルが点滅しているところに
↓のコードをコピー＆ペースト　→　Excel画面に戻り（VBE画面を閉じて）
G列セルを選択してみてください。

Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'この行から//
Dim i As Long
Dim myStr As String, myList As String
If Intersect(Target, Range("G:G")) Is Nothing Or Target.Count > 1 Then Exit Sub
For i = 2 To Cells(Rows.Count, "B").End(xlUp).Row
If Cells(i, "B") <> "" Then
myStr = myStr & Cells(i, "B") & ","
End If
Next i
myList = Left(myStr, Len(myStr) - 1)
With Target.Validation
.Delete
.Add Type:=xlValidateList, Formula1:=myList
End With
End Sub 'この行まで//

B列のデータがリスト候補として表示されるはずです。
（G列のリスト設定は不要です）

※　作業列もVBAもダメなら無視してください。m(_ _)m

この回答へのお礼

引き続きの回答ありがとうございます。
作業列もマクロも使用しないのは難しいみたいですね。
どちらかの方法で進めたいと思います。

お礼日時：2015/09/16 05:48

No.1 回答者： tom04 回答日時： 2015/09/13 21:34

こんばんは！

リスト候補として表示したい列はセル結合されているのですね？

一案です。（やり方だけ）
前提条件として1行目が項目行でデータは2行目以降だとします。

↓の画像のように作業列を使って空白以外のデータを表示させます。
画像ではI2セルに
=IFERROR(INDEX(B$1:B$1000,SMALL(IF(B$1:B$1000<>"",ROW(B$1:B$1000)),ROW(A2))),"")

配列数式になりますので、Ctrl＋Shift＋Enterで確定！
この画面からコピー＆ペーストする場合は
上記数式をドラッグ＆コピー　→　I2セルを選択　→　数式バー内に貼り付け　→　そのまま（編集可能なまま）
Ctrl＋Shiftキーを押しながらEnterキーで確定！
数式の前後に{ }マークが入り配列数式になります。
I2セルのフィルハンドルでずぃ～～～！っと下へコピーしておきます。
これでB列のリスト候補だけが表示されます。

最後にG3セルの入力規則　→　リスト　→　元の値の欄に
=OFFSET(I1,1,,COUNTIF(I:I,"?*")-1)

という数式をいれるとB列2行目以降のデータのみがリスト候補に表示されるはずです。

※　複数セルリスト設定する場合は「元の値」の数式を
絶対参照にしてください。m(_ _)m

この回答へのお礼

さっそくの回答ありがとうございました。

配列数式を使っても作業列（ワークセル）が必要になってしまいますかね。
可能であればこの作業列を使わずに実現したいのですが。
何かいい方法は無い物でしょうか。

お礼日時：2015/09/15 06:45