条件付書式 Sheet1からSheet2に転記した時の転記ミスを反映させたい

以前Sheet1の当該IDをSheet2に転記していけば、色が変わる条件付き書式の件でご質問し、解決いたしました。

今回は、Sheet2側で転記ミスの場合に色が変わる条件付き書式のことをご質問いたします。

添付図でSheet1側にさまざまな情報が入っており、その情報をその都度検討しながら、Sheet2にグループ分けしながらIDを手入力すれば、他の項目は計算式で転記されるようになっております。
グループ分けは自動ではできない事情があり、手入力になってしまいます。

条件付き書式の範囲は、Sheet2の$B$3:$B$14
Sheet1は、A1に日付を入れれば曜日が返され、同曜日とその列に○があれば黄色くなるような条件付き書式が入っています。2行目にフィルターがかかっていて、当該曜日で並べ替えをします。

そこから、Sheet2にIDを転記するのですが、もし該当しない番号を入力した時にエラーがわかるように条件付き書式を入れたいのです。

どなたかご教示いただければありがたいのですが。
宜しくお願いいたします。

No.5 ベストアンサー 回答者： taglet 回答日時： 2022/05/21 15:45

ちょっと長い式になりましたが、これでどうでしょうか。

Sheet2のB3～セルについて、条件付き書式に以下の式をセットしてください。

=AND(B3<>"",IF(ISERROR(VLOOKUP(B3,Sheet1!A:A,1,FALSE())),TRUE(),VLOOKUP(B3,Sheet1!A:H,MATCH(B$1,Sheet1!D$2:H$2,0)+3,FALSE())<>"○"))

「MATCH(B$1,Sheet1!D$2:H$2,0)」で曜日の位置を取得し、IDのセルから位置を数えるので＋３しています。
１つの式にまとめてしまいましたが、作業用のセルに途中の式を格納したほうが保守性は上がると思います。

この回答へのお礼

taglet様

こんな複雑（できる方にはそうでもないか(笑)）なややこしい質問にお答えいただいて、本当にありがとうございました。

見本の方でできたので、やったぁと思って、実際の表に落とし込んだら、赤白逆になってしまったので、よくよくみたら、MATCHに＋3が、実際の表に2でしたので、直したら、見事思った通りの表になりました。

何度もお付き合いいただき、ありがとうございました。

また時々珍問をアップしますので、助けてください。

お礼日時：2022/05/21 17:59

No.4 回答者： taglet 回答日時： 2022/05/21 14:12

Sheet1の条件付き書式は、どのような式になっていますか？

この回答へのお礼

何度もありがとうございます。

まずSheet1で、該当曜日で○のセルに色がつくのは、
=AND(D$3=$B$1,D3="○")　という条件

そして、添付図ではわかりませんが、
Sheet1の該当曜日に○がついているA列のIDセルは、
まず赤くなり、Sheet2に転記されたら、赤が消えるという条件になっています。

手入力は複数人がやるため、転記漏れを防ぐために入れたのです。
それがこちら↓　実はこのサイトで教えていただきました。

=IF(AND($A3,HLOOKUP($B$1,$D$2:$H$13,ROW($A3)-1,FALSE)="○"),COUNTIFS(Sheet2!$B$3:$B$14,$A3)<1,FALSE)

更に、Sheet2側での転記ミスを防ぐために、Sheet2の方に転記ミスがわかる条件を入れたいと思ったわけです。

お礼日時：2022/05/21 15:07

No.3 回答者： taglet 回答日時： 2022/05/21 13:14

空白のセルを赤くしない判定を追加しました。

=AND(B3<>"",IF(ISERROR(VLOOKUP(B3,Sheet1!A:A,1,FALSE)),TRUE,VLOOKUP(B3,Sheet1!A:H,8,FALSE)<>"○"))

No.2 回答者： taglet 回答日時： 2022/05/21 13:04

以下の式をお試しください。

=IF(ISERROR(VLOOKUP(B3,Sheet1!A:A,1,FALSE)),TRUE,VLOOKUP(B3,Sheet1!A:H,8,FALSE)<>"○")

以下の条件に該当する場合、色をつけます。
・B3のIDが、Sheet1のA列に無い
・B3のIDがある行の、A列から数えて8列目が○ではない

この回答へのお礼

すぐにご回答いただき、ありがとうございます。

確かに上の関数だと、該当しないIDは赤くなるのですが、空白のセルも赤くなってしまいます。エラーIDだけ入力したら赤くなるようにしたいのです。

またVLOOKUPで8列目というのは、「金曜日」行しか選択しません。
ややこしいのですが、日にちを入れると曜日も変わるので、その曜日に応じた列の"○"があるIDが正のIDで、それ以外のIDを入れたら赤くなるという条件にしたいのです。

お礼日時：2022/05/21 13:49

No.1 回答者： taglet 回答日時： 2022/05/21 11:27

Sheet2のB3～セルについて、条件付き書式に以下の式をセットしてください。

（ホーム＞スタイル＞条件付き書式＞新しいルール＞数式）

=ISERROR(VLOOKUP(B3,Sheet1!A:A,1,FALSE))

B3が条件付き書式を設定する最初のセル、Sheet1!A:Aが検索対象です。

この回答へのお礼

早速ご回答いただき、ありがとうございます。

Sheet2のB3:B14を範囲指定して、条件付き書式を入れてみたのですが、
空白のセルのみ色がついてしまいます。

Sheet1のA:Aは、検索範囲ではあるのですが、更に条件があり、
この場合は、黄色いセル、「金曜日」に"○"についている同じ行のＡ列、
「3,7,9,10」以外の番号がSheet2に入力されたら、赤くなる、さらに
空白は返さないというふうにしたいのです。

いかがでしょうか。

お礼日時：2022/05/21 12:48