Forum Discussion
Fill in cells based on data from other sheet
I am struggling with a template that I use ( https://templates.office.com/en-us/employee-absence-schedule-tm03987167). I added some sheets that are the basis for the information displayed in the mont...
You can use power query to get data from individual workbooks and then use some formulas on the data to find what you need. The Excel file you provide do not contaion much info about what you need to achieve.
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.
