Excelでの名前定義に空白を使いたい

質問

Excelでの名前定義に空白を使えないのを知りました。
どうしても使いたい場合どのようにしたらよろしいのでしょうか。

この方の質問と少し重複します。
http://okwave.jp/qa3083667.html

不動産会社で契約している大家さんが数名いたとします。
この大家さん達は、アパートやマンションをそれぞれいくつか所有しています。
まずセルA1で大家さんの姓名をプルダウンで選択し、
（ここでどうしても姓名の間に空白が必要なのです）
A2ではそれに対応したアパートやマンションの名称を選択、
A3ではそのアパートの住所を表示(プルダウンでも可)したいとします。

まず他のシートに大家さん名を横並びで一覧を作り、ooya範囲を作って、
それぞれの下にアパート名、その下に住所と交互に記載し、
その範囲を名前の適用で最上行の大家さん名に設定したいのですが、
名前の定義で空白がはねられます。

仮に空白を入れなければ、A1で選んだ大家名に対応して、A2 A3 に
入力規則のセル範囲で、=INDIRECT(A1)　として、
A2ではアパート名 A3では住所を選択して一応使うことはできますが、
このまま表示するので姓名間に空白を入れないわけにはいきません。
(その都度手作業でスペースを入れればいいのかもしれませんが....)

大家　A　　　　　　　　　　　　
Aアパート
Aアパート住所

上記リンクの他の方へのご回答を参考にすると、
禁則文字に対応するリストを作って、VLOOKUPで変換するとありますが、
私の場合は初めの部分で変換が必要なので、ご回答を参考にしても
どのようにしていいのかがわかりません。
ご教授頂ければ幸いです。

回答No.4

rin01 · ベストアンサー

こんにちは～♪

式の書き方はいろいろあって
OFFSET等つかうとすこし短くなりますが。。
再計算するので、INDEXを使っています。

名前定義 その1
名前　→　00ya
=INDEX(Sheet2!$A:$Z,1,1):INDEX(Sheet2!$A:$Z,1,COUNTA(Sheet2!$A$1:$Z$1))

これは大家さんの氏名のリストの式です。
Z列まで(28名分)まで式に入れていませんので必要列に
変更して下さい。少ない場合にはこのままでいいです。
ただ、この式が必要なければ
これまでのmonmeeさんの参照式で構いません。

名前定義 その2
名前　→　siki1
=INDEX(Sheet2!$A$2:$Z$100,0,MATCH(Sheet1!$A$1,ooya,0))

名前定義 その3
名前　→　siki2
=INDEX(Sheet2!$A$2:$Z$100,1,MATCH(Sheet1!$A$1,ooya,0)):INDEX(Sheet2!$A$2:$Z$100,COUNTA(siki1),MATCH(Sheet1!$A$1,ooya,0))

★その2 と　その3 の式でも$F$100 と範囲を指定していますが。
必要範囲に変更して下さい。。
これ以下でしたらこのままで構いません。。

次に
A1の入力規則
リスト　→　=ooya

A2A3の入力規則
リスト　→　=siki2

で、終了です。。。

上の式はチョット長いのでコピーして貼り付ける時は
Ctlr＋Vキーで貼り付けてください。
(ご存知でしたらゴメンナサイ！！）

ご参考にどうぞ。。。

。。。Ms.Rin～♪♪

お礼

ご親切ありがとうございます。
やってみます。

回答No.5

noname#79209

要は、最終的に大家さんの姓名間に空白を入れたいだけでしょう？
プルダウンでは「山田　太郎」のように空白入りにしておき、
名前定義での名前は「山田太郎」とつけておき
=INDIRECT(SUBSTITUTE(A1,"　",""))
とすれば「山田　太郎」は「山田太郎」と変換されますから。

お礼

ありがとうございます。
何でもそうだと思いますが、
精通していらっしゃる方には簡単でも、
不慣れな者には難しいようです。
またご指南ください。

回答No.3

rin01

ふたたび～です。。。♪

先の表がずれちゃって、わかりにくいと思いますが。。。

たとえば、
最初の表が、Sheet1　リストがある表がSheet2とします。
Sheet2の1行目が、大家さんの氏名
佐藤　AA と　山田　BB(氏名の間に、全角スペースあり)
各列のデータは、A5までとB7まで。。

ご質問の
>その範囲を名前の適用で最上行の大家さん名に設定したいのですが、
>名前の定義で空白がはねられます。

名前定義は、スペースを取った名前で定義します。。
たとえば、佐藤　AA →佐藤AA　
参照範囲　=Sheet2!$A$2:$A$5

そして、
>A1で選んだ大家名に対応して、A2 A3 に
>入力規則のセル範囲で、=INDIRECT(A1)　として、

=INDIRECT(A1)
を以下に変更します。。
=INDIRECT(SUBSTITUTE(A1,"　",))
　　　　　　　　　　　　　 　　　　　　↑
　　　　　　　　　　 　　　　　　全角スペース
この式は、A1の値の全角スペースを取って
佐藤　AA を　佐藤AAに変換する式です。
スペースが、半角の場合は式の↑の部分を " "にして下さい。

これでご希望通りになると思います。
ただ、

>その範囲を名前の適用で最上行の大家さん名に設定したいのですが

ですと、１つ１つ大家さんの範囲を名前定義しなくては
いけないので面倒ですよネ。。。。

大家さんが、少なければいいですが。
もし多い場合、これを１つの式で名前定義して入力規則のリストに
する方法もありますので。
ご希望でしたら、回答いたします。。。

ご参考にどうぞ。。。

。。。Ms.Rin～♪♪

補足

Ms.Rinさん、すばらしい！うまく行きました。
もしお時間ありましたら、
>１つの式で名前定義して入力規則のリストに
する方法　
教えて頂けるとありがたいです。

回答No.2

rin01

すみません～♪

間違って　↑　表だけアップしちゃいました。。
お昼休みなので、あとで回答します。。

。。。Ms.Rin～♪♪

補足

ありがとうございます。
お待ちしています！

回答No.1

rin01

A
[1] 山田　B ▼
[2] ccc　　　▼
[3] 333　　　▼

A B
[1] 佐藤　AA 山田　BB
[2] aaa ccc
[3] 111 333
[4] bbb ddd
[5] 222 444
[6] eee
[7] 555