条件に該当するセル(を含む列)を別シートに転記

質問

Excel(正しくはGoogleスプレッドシート)で、以下のような表を作成する方法について教えていただきたいです。
-----------------------------------
Sheet1
＿＿＿Ａ＿│＿Ｂ＿
１│商品名│状況＿
２│イチゴ│入荷済
３│スイカ│入荷待
４│ナシ＿│入荷待
５│ブドウ│入荷待
６│ミカン│入荷済
７│モモ＿│入荷済
８│リンゴ│入荷待
-----------------------------------
Sheet1には「商品名」と「状況(入荷待or入荷済)」が記入されています(商品名はアイウエオ順に並んでおり、今後も増えていく予定です)。
この「状況」が「入荷待ち」のもののみをSheet2に転記し、かつ入荷予定日を記入する欄を追加したいです。
-----------------------------------
Sheet2
＿＿＿Ａ＿│＿Ｂ＿│＿＿Ｃ＿＿
１│商品名│状況＿│入荷予定日
２│スイカ│入荷待│4月18日＿
３│ナシ＿│入荷待│4月15日＿
４│ブドウ│入荷待│4月17日＿
５│リンゴ│入荷待│4月15日＿
　　　　　　　↓
＿＿＿Ａ＿│＿Ｂ＿│＿＿Ｃ＿＿
１│商品名│状況＿│入荷予定日
２│スイカ│入荷待│4月18日＿
３│ナシ＿│入荷待│4月15日＿
４│ブドウ│入荷待│4月17日＿
５│メロン│入荷待│4月15日＿
６│リンゴ│入荷待│＿＿＿＿＿
※Sheet1にメロンを追加するとSheet2のC列がズレてしまう
-----------------------------------
「入荷待ち」のもののみをSheet2へコピーすることはできたのですが、商品名と状況の欄は増えても入荷予定日欄が増えず、新しく商品が追加された場合に商品名＆状況と入荷予定日の欄がズレてしまいます。
具体的な例を出しますと(各シート商品名のセルをA1として)Sheet1に「入荷待」の「メロン」が追加された場合、Sheet2のA5に「メロン」B5に「入荷待」がコピーされますが、C5は追加されないため「4月15日」のままになり、リンゴの入荷予定日が空欄となってしまいます。本来はC5を空欄(メロンの入荷予定日を記入する)に、C6はリンゴの入荷日として4月15日をズラしたいのですが…。

現在使っている関数はネット上で拾ってきたもののため自分でもよくわかっておらず、また関数ではC列の行の追加はできない雰囲気のため1から作りなおすつもりです。
おそらくVBAなどを使う必要があるのではないかと思っておりますが、解決方法をご教授いただけると幸いです。

回答No.1

noname#203218 · ベストアンサー

シート１に入荷予定日、入荷日を入力するようにすれば、filter関数で自動的にシート２に入荷待表を作成出来ます。
シート１で入荷日を入力すると入荷済と表示され、シート２に抽出したデータからも項目が削除されます。

添付図に沿った数式説明となります。（上段がシート１　下段がシート２）
シート１
B２式=if(A2<>"",if(D2<>"","入荷済","入荷待"),"")
下方にフィルコピー

シート２
A2（品名）式=filter('シート1'!A:A,'シート1'!B:B="入荷待")
B2（状況）式=filter('シート1'!B:B,'シート1'!B:B="入荷待")
C2（予定日)式=filter('シート1'!C:C,'シート1'!B:B="入荷待")

抽出関数は下方へのコピペをしなくても=CONTINUEで自動的に下方の式が作成されます。

シート１　C、D列　、　シート２　C列は日付を表示する列なので表示型式の詳細設定で09/26日を選択しています。

不要な情報であればスルーして下さい。

お礼

ご回答ありがとうございます。
質問には記載しておりませんでしたが、Sheet1は社内で使用するシート、Sheet2は社外(取引先等)で記入するシートとしており、入荷予定日の欄は外部で記入してもらうためどうしてもSheet2に予定日の記入欄を作成する必要があります。
わざわざお答えいただいたのに申し訳ありませんm(__)m