同一セルに入力規則のリストと参照する設定併用できるような設定はありますでしょうか！！

Question

始めて相談させていただきます。

エクセルにて予定表を作成しています。
受注残が別のシートにまとまっています。

１シートは「受注残シート」からコピペして入力するのみの「入力シート」
１シートは「入力シート」から参照をして表にする「作表シート」（見栄え、短時間作成重視）

参照設定をしているためコピペするだけで、簡単かつ手間がかからず作表できる仕様になります。

ですが、受注残には表示されない「在庫品」が存在するため、参照設定をしている同一のセル（軽く関数を使っています）に入力規則でリストから入力するように設定をしました。

するとその時は簡単にリストから選べることができますが、当たり前ですが、次回コピペをしてもリストから選択をしているため（参照設定が消えているため）「作表シート」には反映されません。


リストに参照する関数を入れ、選択できるようにしましたが、一つずつリストを出して選択しなければなりません。（入力シートから入力するだけでパッと参照をするのが希望、、、）

在庫品のみの表や、テキストボックスで入力するのは時間がかかるためあまり好みません。

参照と入力規則のリストが併用できるようなマクロ、リストから選択しても参照設定が消えない設定方法は何かございますでしょうか。

伝えられているかとても不安ですが、よろしくお願いいたします、、、、

tatsumaru77 · Accepted Answer

作表シートに設定する関数式の列は、
B,C,D,E,F,G,H,M列で間違いないでしょうか。これで全てでしょうか。
以下のマクロは、上記列に関数式を設定します。
No7のマクロは確認がすんだら、破棄してください。

Option Explicit
Public Sub 参照設定()
    Const Bfmt As String = "=SUBSTITUTE(SUBSTITUTE(入力のみ!$B$RRR,""有限会社"",""㈱""),""株式会社"",""㈱"")"
    Const Cfmt As String = "=入力のみ!$C$RRR"
    Const Dfmt As String = "=入力のみ!$D$RRR"
    Const Efmt As String = "=入力のみ!$E$RRR"
    Const Ffmt As String = "=入力のみ!$F$RRR"
    Const Gfmt As String = "=LEFT(入力のみ!$M$RRR,7)"
    Const Hfmt As String = "=IF(入力のみ!$H$RRR="""","""",入力のみ!$H$RRR+1)"
    Const Mfmt As String = "=入力のみ!$M$RRR"
    
    Dim wrow As Long        '作表シート行番号
    Dim srow As Long        '入力のみシート行番号
    Dim ws As Worksheet
    Dim weekNo As Long      '曜日番号
    Dim seqNo As Long          '同一曜日内通番
    Set ws = Worksheets("作表")
    
    For weekNo = 1 To 5
        For seqNo = 1 To 5
            wrow = (weekNo - 1) * 5 + seqNo + 3
            srow = (weekNo - 1) * 6 + seqNo + 3
            ws.Cells(wrow, "B").Formula = get_fmt(Bfmt, srow)
            ws.Cells(wrow, "C").Formula = get_fmt(Cfmt, srow)
            ws.Cells(wrow, "D").Formula = get_fmt(Dfmt, srow)
            ws.Cells(wrow, "E").Formula = get_fmt(Efmt, srow)
            ws.Cells(wrow, "F").Formula = get_fmt(Ffmt, srow)
            ws.Cells(wrow, "G").Formula = get_fmt(Gfmt, srow)
            ws.Cells(wrow, "H").Formula = get_fmt(Hfmt, srow)
            ws.Cells(wrow, "M").Formula = get_fmt(Mfmt, srow)
        Next
    Next
End Sub

Private Function get_fmt(ByVal trg_fmt As String, ByVal srow As Long) As String
    Dim srow_str As String
    srow_str = srow
    get_fmt = Replace(trg_fmt, "RRR", srow_str)
End Function

tatsumaru77 · Answer

>無知で大変申し訳ございませんが、No.7とNo.8どちらも設定すればよろしいでしょうか。

No8のみを設定してください。
（No7はお試し版なので無視して構いません）

tatsumaru77 · Answer

補足が途中で切れていたので、作表シートのB列とH列のみをマクロで
設定するサンプルです。
B列とH列は、リストによる入力がある。
他の列はない。という前提です。
とりあえず、上記の前提でのマクロです。
標準モジュールに登録して実行してください。

Option Explicit

Public Sub 参照設定()
    Dim wrow As Long        '作表シート行番号
    Dim srow As Long        '入力のみシート行番号
    Dim ws As Worksheet
    Dim weekNo As Long      '曜日番号
    Dim seqNo As Long          '同一曜日内通番
    Dim addr1 As String
    Dim addr2 As String
    Set ws = Worksheets("作表")
    For weekNo = 1 To 5
        For seqNo = 1 To 5
            wrow = (weekNo - 1) * 5 + seqNo + 3
            srow = (weekNo - 1) * 6 + seqNo + 3
            addr1 = "$B$" & srow
            addr2 = "$H$" & srow
            ws.Cells(wrow, "B").Formula = "=SUBSTITUTE(SUBSTITUTE(入力のみ!" & addr1 & ",""有限会社"",""㈱""),""株式会社"",""㈱"")"
            ws.Cells(wrow, "H").Formula = "=IF(入力のみ!" & addr2 & "="""","""",入力のみ!" & addr2 & "+1)"
        Next
    Next
End Sub

tatsumaru77 · Answer

No５です。
１．あなたの環境は、マクロが実行可能でしょうか。
以下のマクロを標準モジュールに登録してください。
添付図のようなメッセージボックスが表示されればOKです。
表示されなければ、マクロを実行できる状態になっていません。
まず、マクロを実行できる状態にすることが最初に行うべきことです。
Public Sub マクロ試験()
    MsgBox ("マクロ試験が成功しました")
End Sub

２．マクロを作成するには情報がたりないです。
現在の不明点は以下の通りです。
①作表シートのシート名が不明。シート名は「作表」ですか、それとも「作表シート」ですか。もしくは、それ以外ですか。

②入力シートのシート名が不明。関数式に「入力のみ」とあるので、「入力のみ」が入力シートでしょうか。

③縦列に月から金までの日付とありますが、何列の何行から何行までが月～金の日付ですか。
例　A1～A5、B3～B7等のように補足してください。

④横列に得意先、品名、数量、納期等
関数式を埋め込んでいる列のみでかまわないので、その列を提示してください。
例　得意先：B列、納期：D列のように補足してください。

⑤得意先の列=SUBSTITUTE(SUBSTITUTE(入力のみ!$B$12,"有限会社","㈱"),"株式会社","㈱")
これが、設定されている最初の行のセルの位置を提示してください。
例　B3のように補足してください。
次の行も同じ関数式が設定されているなら、その旨、補足してください。
その場合、何行まで設定されていますか。
例　B3～B7のように補足してください。
この関数式の行と列は、絶対参照なので、全て同じ内容が、全てのセルに設定してあると理解しましたが
間違いないでしょうか。

⑥納期の列=IF(入力のみ!$G$11="","",入力のみ!$G$11+1)
これが、設定されている最初の行のセルの位置を提示してください。
例　D3のように補足してください。
次の行は、=IF(入力のみ!$G$11="","",入力のみ!$G$11+2)
になっていますか。
それとも、=IF(入力のみ!$G$11="","",入力のみ!$G$11+1)でしょうか。
その場合、何行まで設定されていますか。
例　D3～D7のように補足してください。

⑦他はほぼ参照設定のみです。
設定している範囲とその内容を提示してください。

⑧リストから選択し入力することが行われる列は、
⑤⑥の列と理解しました。
⑦も該当するのでしょうか。
もし、該当しない場合、⑦の補足は不要です。

tatsumaru77 · Answer

対策として簡単に思いつくのは、
１．「作表シート」から「入力シート」を参照をするように関数式を埋め込むマクロを作成しておく。
２．「受注残シート」からコピペして「入力シート」へ貼り付ける。
３．貼り付けた直後に、上記の１のマクロを実行する。
（マクロは人間が手動で実行します。マクロを実行すると、入力規則のリストで入力された内容はなくなります。そのセルは、「入力シート」の内容を表示します）

まず必要なのは、１のマクロを作成することですが、そのためには、以下の情報が必要です。
１．入力シートの正確なレイアウト
２．作表シートの正確なレイアウト
３．作表に設定している関数式の内容

tatsumaru77 · Answer

>エクセルがあまり得意でないため、お時間ございましたら詳細に設定方法をご教授いただけますでしょうか。

ほかの方も言われていますが、詳細な設定方法を提示するためには、
あなたが提示されているシート（入力シート、作表シート）の正確なレイアウトが判らないと提示できません。
正確なレイアウトをあなたが提示されれば、良い回答が得られやすくなります。

河内のおやじ · Answer

具体的に簡易データの表お願いします。

fujillin · Answer

こんばんは

ご質問文の説明が曖昧なので、よくわかりませんけれど・・
一つのセルに参照式と入力規則を同時に設定することはできません。

ですので、二つのセルにその機能を分けて（どちらが優先なのか知りませんけれど、勝手に想像するなら）、一方の参照値に応じて入力規則のリストの内容を変更できるようにしておけば、ご希望のようになるのかも知れないと思いますけれど・・

あるいは逆であれば、入力規則で選択した値に応じて、参照先が変わるような式にしておけば良いとかではないのかな。

内容が全く分からないので、これ以上は何ともわかりかねますがヒントにでもなれば。

河内のおやじ · Answer

Vlookup または、xlookup関数を使って別表から持って来る様にしてください。

同一セルに入力規則のリストと参照する設定併用できるような設定はありますでしょうか！！

作表シートに設定する関数式の列は、

>無知で大変申し訳ございませんが、No.7とNo.8どちらも設定すればよろしいでしょうか。

補足が途中で切れていたので、作表シートのB列とH列のみをマクロで

No５です。

対策として簡単に思いつくのは、

>エクセルがあまり得意でないため、お時間ございましたら詳細に設定方法をご教授いただけますでしょうか。

具体的に簡易データの表お願いします。

こんばんは

Vlookup または、xlookup関数を使って別表から持って来る様にしてください。

似たような質問が見つかりました

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人がよく見るQ&A

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング