ここから本文です

下記のようなエクセルの一覧リストがあります。

アバター

ID非公開さん

2017/3/2612:31:17

下記のようなエクセルの一覧リストがあります。

部署___名前___内線番号
総務部_田中___123
総務部_山田___234
総務部_加藤___34
営業部_山中___333
営業部_本田___343
営業部_丸山___888
経理部_近藤___333

別のシートで下記のように部署名に「総務部」を入れると、隣の名前欄をプルダウンで「田中」「山田」「加藤」が選べるようにしたいのですが、どうしたらいいでしょうか。新たにほかのシートは作りたくないです。また★印のところはDGET関数で対応します。

部署___名前___内線番号
総務部_●●●●___★★★

閲覧数:
85
回答数:
4
お礼:
25枚

違反報告

ベストアンサーに選ばれた回答

プロフィール画像

カテゴリマスター

tai********さん

2017/3/2613:56:18

<新たにほかのシートは作りたくない>

入力規則の元の値に入れることができるのは範囲を表す数式とカンマで区切った値だけです。ですからどこかにリストを作るほかないので新たなシートを作りたくなかったら同じシートのどこかあいているセル範囲を使ってリスト作ります。それではほかのシートに作ったのと同じだ、というならあきらめるしかないです。元の値に範囲を直接表す数式以外の数式を入力すると警告が出ます。

リストを作るにはフィルタや数式、ピボットテーブルを使います。自動で反映されるのは数式だけですがリストに空白が入ってしまいます。空白セルを除くように名前を付ける方法もあります。

どうしても別のセルを使いたくないときはマクロでリストを配列に入れるといいでしょう。

  • tai********さん

    2017/3/2614:30:22

    例えばA1:A10は部署のリストが設定済み、
    B1:B10に名前のリストを設定しますがA列で部署を選んだときイベントが発生してB列に名前のリストが設定されます。

    シートモジュールに下のコードをコピペします。

    Private Sub Worksheet_Change(ByVal target As Range)
    Dim i As Long, j As Long, Namae()
    If target.Count > 1 Then Exit Sub
    If Intersect(target, Range("A1:A10")) Is Nothing Then Exit Sub
    For i = 2 To Cells(Rows.Count, 4).End(xlUp).Row
    If target.Value = Cells(i, 4) Then
    ReDim Preserve Namae(j): Namae(j) = Cells(i, 5)
    j = j + 1
    End If
    If Not Not Namae Then
    target.Offset(, 1).Validation.Delete
    target.Offset(, 1).Validation.Add Type:=xlValidateList, Formula1:=Join(Namae, ",")
    End If
    Next
    End Sub

返信を取り消しますが
よろしいですか?

  • 取り消す
  • キャンセル

この回答は投票によってベストアンサーに選ばれました!

ベストアンサー以外の回答

1〜3件/3件中

並び替え:回答日時の
新しい順
|古い順

nis********さん

2017/3/2614:21:23

部門リストを下図のようにします。
◆D1:F4 の範囲を選択(ドラッグ)します。
[数式] → [選択範囲から作成] を選択
表示されるダイアログで、[上端行] のみにチェックをいれる
以上で各部門名の名前がそれぞれ定義されます。

◆B1セルを選択して
[データ] → [データの入力規則]を選択します。
データの入力規則(V)
入力値の種類(A) → リスト
元の値 → INDIRECT(A1)
と設定します。

蛇足
A1の入力規則
入力値の種類 → リスト
元の値 → =$D$1:$F$1(D1:F1をドラッグ)

部門リストを下図のようにします。
◆D1:F4 の範囲を選択(ドラッグ)します。
[数式] →...

よこまやさん

2017/3/2613:37:21

その一覧のシート名が仮にSheet2として
尚且つ部署名は並び替えで整列しているとします。
(要は連続していないとリストに使えないので。)

別のシートの部署名がA2でリストを設置したいのが
B2とします。

そこで入力規則のリストを用いるのですが
別シ-トのリストで入力規則を使うときは
名前定義が必須です。

名前定義の時その数式が選択セルに依存するので
設置したいB2セルを選択して名前定義してください。

名前は 範囲 で定義しましたが
勿論なんでも構いません

=OFFSET(Sheet2!$A$2,MATCH(A2,Sheet2!$A$2:$A$8,0)-1,1,COUNTIF(Sheet2!$A$2:$A$8,A2),1)
とします。

式中の
Sheet2!$A$2:$A$8
は件数に合わせてください。
あるいは別途可変領域設定の名前定義を
用いてもいいです。

で、リストの話に戻りますが
元の値には
=範囲
と定義した名前を書けば
可能ではないかと。

愛子さん

2017/3/2613:32:10

みんなで作る知恵袋 悩みや疑問、なんでも気軽にきいちゃおう!

Q&Aをキーワードで検索:

Yahoo! JAPANは、回答に記載された内容の信ぴょう性、正確性を保証しておりません。
お客様自身の責任と判断で、ご利用ください。
本文はここまでです このページの先頭へ

「追加する」ボタンを押してください。

閉じる

※知恵コレクションに追加された質問は選択されたID/ニックネームのMy知恵袋で確認できます。

不適切な投稿でないことを報告しました。

閉じる