エクセルの条件付き書式 個人シートを参照して集計シートに色付けしたい

個人シートは３枚あり集計シートに反映されるようになってます。
個人シート　シート名　山田　佐藤　鈴木　とします。
A列　番号（１から20）　B列　商品名　C列　納品日　D列　配置日　E列　特注品

集計シート　
A列　個人名　B列からU列まで　商品名

やりたいこと
●個人シートの配置日が空欄でなければ集計シートの商品名のセルを青で塗りつぶす
●配置日が本日を超えたら集計シートの商品名のセルを緑でぬりつぶす
●特注品に〇があれば集計シートの商品名の文字の色を赤にする

以下はやったことです
集計シートのB2セルの数式です。A2セルは山田
=INDEX(山田!$A$2:$B$21,COLUMN(山田!A3),2)& ""

なので２行目は山田の担当した商品がB2からU2までの２０品分並んでいます。
３行目は同様に佐藤の担当した商品がB3からU3まで２０品分
４行目は鈴木の担当した商品がB4からU4まで２０品分
実際はそれぞれ5品分だったりです。増えていきます。商品名は規定なものはなくどんどん
新しい名前できます。（最初の三文字だけ表示したい）

集計シートをみれば商品名の色付き方で状態が一目でわかるようにしたいです。
集計シートの商品名に条件付き書式を設定して個人シートの日付を参照すれば、、と
考えたのですがわかりませんでした。

参考になればといろんなものを見たり調べたりしたのですが元々よくわかってないので混乱するばかりです。ちなみにマクロは全くわかりません。

どおか詳しい方ご教示いただけませんでしょうか。
よろしくお願いいたします。

質問者からの補足コメント

• 早速のご回答ありがとうございます。
  せっかく考えてくださったのに私の質問の仕方がわかりづらく
  ご面倒おかけしました。
  早速回答どおりにやってみたのですが、やりたいことはできませんでした。
  
  やりたいことを画像で貼り付けてみました。
  集計シートのB２セルに条件付き書式を実行して書式をコピーしたいのですが、参照先が固定されてしまい色が反映されません。
  山田、佐藤、鈴木シートは列で降りていくデータに対し集計シートは列で横に進むからでしょうか？
  
  エクセルを全然わかっていないのにこのような質問をしてしまい申し訳ございません。
  何か教えていただけますと大変助かります。

  
  No.1の回答に寄せられた補足コメントです。 補足日時：2023/06/22 20:50

No.1 回答者： kantansi 回答日時： 2023/06/22 12:18

おっしゃる内容を理解しました。

以下の手順に従って、条件付き書式を使用して集計シートの商品名を色付けする方法を説明します。

集計シートで、B2セルからU4セルまでのセル範囲を選択します（B2からU4までのセルを含む）。
「ホーム」タブに移動し、「条件付き書式」のセクションから「新しいルール」を選択します。
「新しい書式ルール」ダイアログボックスが表示されたら、「ルールの種類」で「書式のルールを使ってセルの値を書式設定する」を選択します。
「ルールの記述」として以下の条件を入力します：
「セルの書式設定式」に=AND($D2<>"",$D2<TODAY())を入力します。これは、配置日が空欄でなく、配置日が本日を超えている場合に該当するセルに適用されます。
「書式設定」で「塗りつぶし」タブを選択し、緑色の塗りつぶしを選択します。
「適用範囲」には集計シートのセル範囲が既に選択されているはずです。
「OK」をクリックしてルールを適用します。
これにより、配置日が空欄でなく本日を超えているセルが緑色で塗りつぶされます。

次に、特注品の条件を設定します。

集計シートで、B2セルからU4セルまでのセル範囲を再度選択します。
「新しい書式ルール」ダイアログボックスを開き、「ルールの記述」として以下の条件を入力します：
「セルの書式設定式」に=AND($E2<>"",$E2="〇")を入力します。これは、特注品が〇であるセルに適用されます。
「書式設定」で「フォント」タブを選択し、赤色の文字色を選択します。
「適用範囲」には集計シートのセル範囲が既に選択されているはずです。
「OK」をクリックしてルールを適用します。
これにより、特注品のセルの文字が赤色になります。

以上の手順で、集計シートの商品名セルに条件付き書式を設定し、所望の色付けを実現できるはずです。条件付き書式を使用することで、個人シートの日付を参照して色の変更を行います。ご参考までにお試しください。

この回答へのお礼

この度は早々の回答ありがとうございました。
結果的に別シート横軸に伸びる集計表は作れませんでしたが、1人ずっと悩んでいた時に回答をいただけたのは本当に嬉しかったです。
本当にありがとうございました。

お礼日時：2023/06/29 12:56