両シートとも何というシート名なのか説明がありませんので、取り敢えず仮の話として上段の表があるシートがSheet1、下段の表があるシートがSheet2であるものとします。
それと、
>翌月以降は日付と曜日が当然の様に異なるので毎回数式を組み直す必要が出てしまうので、日付と名前から検索してシフト時間を引っ張ってきたいのですが
との事ですが、同じ日付の日であっても年によって曜日は変わって来ますので、「11月シフト表」というだけでは、何年の11月であるのかが判らないため、その月の何日が何曜日であるのかが定まりません。
ですから、Sheet1の適当なセルに年月を指定するための日付データを入力する様にして下さい。
具体的にはまず、Sheet1のK1セルの書式設定を[ユーザー定義]の
yyyy"年"m"月"
か又は
gge"年"m"月"
としておいた上で、Sheet1のK1セルの右隣のセルに「シフト表」と入力して下さい。
そして、Sheet1のK1セルに年月を入力する際には、例えば
2015/11
や
2015年11月
といった形式で年月のデータを入力して下さい。
次に、Sheet1のK2セルに次の関数を入力して下さい。
=IF(ISERROR(1/DAY($K$1)),"",IFERROR((TEXT($K$1,"yyyy/m/")&COLUMNS($K:K))+0,""))
次に、Sheet1のK2セルの書式設定を[ユーザー定義]の
d"日"
に設定して下さい。
次に、Sheet1のK3セルに次の関数を入力して下さい。
=IF(K$2="","",TEXT(K$2,"aaa"))
次に、Sheet1のK3セルの書式設定を[ユーザー定義]の
aaa
に設定して下さい。
次に、Sheet1のK2～K3のセル範囲をコピーして、Sheet1のL2～AQ3のセル範囲に貼り付けて下さい。
次に、Sheet2のA1セルに次の関数を入力して下さい。
=IF(ISERROR(1/DAY(Sheet1!$K$1)),"(年月未入力)",TEXT(Sheet1!$K$1,"yyyy年m月シフト表"))
次に、Sheet2のA1セルの書式設定を[ユーザー定義]の
yyyy"年"m"月シフト表"
か又は
gge"年"m"月シフト表"
に設定して下さい。
次に、Sheet2のB3セルに次の関数を入力して下さい。
=IFERROR(COUNTIF(INDEX(Sheet1!$K:$AO,MATCH($D$1,Sheet1!$F:$F,0),),"*?-*?"),"")
次に、Sheet2のB3セルの書式設定を[ユーザー定義]の
d"日"
に設定して下さい。
次に、Sheet2のA6セルに次の関数を入力して下さい。
=IF(ISERROR(1/DAY(Sheet1!$K$1)),"",IFERROR((TEXT(Sheet1!$K$1,"yyyy/m/")&(ROW()-ROW($A$6))/2*7+COLUMNS($A$6:A6)-WEEKDAY(Sheet1!$K$1)+1)+0,""))
次に、Sheet2のA6セルの書式設定を[ユーザー定義]の
d"日"
に設定して下さい。
次に、Sheet2のA7セルに次の関数を入力して下さい。
=IF(OR(A6="",$D$1=""),"",IF(COUNTIF(Sheet1!$F:$F,$D$1),INDEX(Sheet1!$K:$AO,MATCH($D$1,Sheet1!$F:$F,0),MATCH(A6,Sheet1!$K$2:$AO$2,0))&"",""))
次に、Sheet2のA6～A7のセル範囲をコピーして、Sheet2のA6～G17のセル範囲に貼り付けて下さい。
尚、Sheet2のE3セルの勤務時間数を表示させるための関数に関しては、1回目の休憩時間が何時から何時までで、2回目の休憩時間が何時から何時までで、3日回目の・・・と言う具合に、各々の休憩時間が何時から何時までなのかという事に関して質問者様から何の御説明も無いため、情報不足で関数を組む事が出来ません。
補足
色々と情報不足で申し訳ありません。 シート名は上段がSheet1、下段はSheet2とします。 Sheet1の日付は=DATE(2015,$K$1,1)で「d"日"」になっています。日付欄の上の黄色部に11(表記は11"月")が入っています。曜日は日付から引っ張ってきてaaaで表記しています。 Sheet2は日付・曜日ともに手入力になっています。 ※Sheet1は以前に別の人が作成したため、この様な仕様になっているのか理由は分かりません。