エクセル2007入力規制リストで空白をなくしたい！

質問

エクセル2007入力規制リストで空白をなくしたい！
開発用テキストっていう名前の別シートのE4からE100にある項目を空白を無視するようにって可能でしょうか…
調べてもわからず…
よろしくお願いいたします！

回答No.8

msMike

[No.5補足]へのコメント、
》 別シートへの参照はできませんと出ます…
その原因は貴方の Excel のバージョンが古過ぎるのかも。
だったらハナシは簡単でしょ？エラーメッセージに従えば良いだけのことかと。
つまり、“リスト”を同一シート内の空きエリアに設ければヨカンベ～ｯ！
例えば、Sheet1 の空き列(E列でも、Z列でも)の 範囲 $E$4:$E$100 または $Z$4:$Z$100を List なる範囲名として名付けておけば、式を次式に変更して試してみてください。
=OFFSET(INDIRECT(CELL("address",List)),,,COUNTA(List),)

回答No.7

chayamati

回答№4の添付図です

回答No.6

chayamati

回答ではありませんが
元の値列の先頭を空白にする。
これで、一つの空白に続いて有効値がリストされます。

回答No.5

msMike

添付図参照(Excel 2019)

Sheet1 のセル A1 に設定した[入力規則]
セル A1 を選択⇒[データ]⇒[データツール <データの入力規則>]を
実行⇒[設定]タブにおいて、[入力値の種類]ボックスで“リスト”を
選択⇒[元の値]ボックスに下記の式を入力⇒[OK]をパシーッ
=OFFSET(開発用テキスト!E$4,,,COUNTA(開発用テキスト!$E$4:$E$100),)
オ・シ・マ・イ

補足

教えて頂いた数式を入れたら別シートへの参照はできませんと出ます…

回答No.4

imogasi

＃２，＃３です。
vbaでやるとどの程度になるか、をやって見ました（趣味？）。
主たる部分はWEB記事にある。
標準モジュールに、下記を張り付け実行。
データ例は＃３のものを使うとする。
Sub validation_test2()
'今設定されている入力規則を削除しておく
Sheets("Sheet3").Range("A1:A10").Validation.Delete
'入力規則を設定
'-----空白セルの値を捨てる---
lst = ""
For Each cl In Sheets("開発").Range("A1:A50") '多めのセル数を指定
If cl <> "" Then
lst = lst & cl & ","
End If
Next
lst = Left(lst, Len(lst) - 1)
'---
Sheets("Sheet3").Range("A1:A10").Validation.Add Type:=xlValidateList, Formula1:=lst
End Sub
この「'-----空白セルの値を捨てる---」以下７行が本件で増える部分。

回答No.3

imogasi

＃２です。＃２の補足で、参考に。
其の後、実例的にやって見ると
「開発」（当方の例のシート名）シートの
A1:A20
ああ１
いい２

いい３
ああ３

ああ４
いい５
ああ５
いい６

ああ７
いい８
ああ８

いい１０
ああ１０
いい１１
とする。
「開発」シートのB1に式
=INDEX(A:A,SMALL(IF(A:A<>"",ROW(A:A)),ROW()))
を入れてSHIFT+CTRL+ENTERを押す。配列数式。
A列でデータ個数のあるB１４まで式を複写。B１４の１４個は目視で判断で行う。
結果　B1:B14
ああ１
いい２
いい３
ああ３
ああ４
いい５
ああ５
いい６
ああ７
いい８
ああ８
いい１０
ああ１０
いい１１
ーー
Sheet1（当方のやったシート例）の
A列でA1:A20（当方の例）を範囲指定して、
入力規則のリストでデータ範囲を，=開発!B1:B14
と指定。
＝＝
上記の「配列数式」などやったことあるかな。
フィルタ操作でB列に空白行を省いたデータを作り（戦記WEB記事にある）そのセル範囲（B1：B１４）を指定するほうが、よさそうだ。

回答No.2

imogasi

元データの空白セルを省き、上行に詰めるのは、
具体的にシートにそういうデータをつkらないと、だめなようです。
操作画面での、「空白を無視するは」のチェックを入れる、は関係ないようだ。
（１）VBAでやる
（２）関数を使う
（３）フィルタ―を使う
の（２）でやりたいのだろう、
Gooleで「エクセル リスト 空白 詰める　関数」で照会で出てきたよ。
http://office-qa.com/Excel/ex257.htm
空白セルを詰めて表示する方法
質問者とバージョンが違うが大丈夫でしょう。

回答No.1

wakobata

>エクセル2007入力規制リストで空白をなくしたい！

excel2007はサポートが終了しているので施用は中止してください。

>開発用テキストっていう名前の別シートのE4からE100にある項目を空白を無視するようにって可能でしょうか…

それを見ているわけではないのでわかりませんが、できるのではないでしょうか。