Forum Discussion
Fill in cells based on data from other sheet
I need to achieve the similar result displayed on the first three sheets based on the data on sheets P, K, H, D.
First, I'd do tables for all types of off-days and in exactly the same format. For example, A is missed.
Second, you need actual dates, not only weekday names or days numbers. As variant, instead of
=TEXT(WEEKDAY(DATE(CalendarYear,1,1),1),"aaa")use
=DATE(CalendarYear,1,column()-column($B$1))and in above perhaps for month number will work =MONTH(1&$B$4)
With that you may use something like
=IF(COUNTIFS(tblA,tblAnames,name, tblAstart, "<=" $ day, tblAend, ">=" $ day),"A",
IF(COUNTIFS(tblB,tblAnames,name, tblBstart, "<=" $ day, tblBend, ">=" $ day),"B",
....
)))Thanks for the advice. I'll try to get the file reorganized and formulas adjusted.
I have reorganized the file but can't get the formula correct. Can you please help me?
You initial formula is
=IF(COUNTIFS(P!$C$3:$C$14,$D9,P!$E$3:$E$14,">=January!F$4",P!$F$3:$F$14,"<=January!F$4"),"P")
is to be changed on
=IF(COUNTIFS(P!$C$3:$C$14,$D9,P!$E$3:$E$14,"<="&January!F$4,P!$F$3:$F$14,">="&January!F$4),"P","")In criteria it shall be like "="&A1, not "=A1"
Plus, you concatenate names, please be sure you compare correct column(s) / cell(s) with names.
In attached file formulas for January and Katrin are corrected.