Forum Discussion
Need help on what formulas to use to able to complete a table
=IF(AND(WEEKDAY($A2)=2,C$1="Monday"),INDEX(Tabelle2[hours],MATCH(1,($A2=Tabelle2[Date])*($B2=Tabelle2[employee]),0)),IF(AND(WEEKDAY($A2)=3,C$1="Tuesday"),INDEX(Tabelle2[hours],MATCH(1,($A2=Tabelle2[Date])*($B2=Tabelle2[employee]),0)),IF(AND(WEEKDAY($A2)=4,C$1="Wednesday"),INDEX(Tabelle2[hours],MATCH(1,($A2=Tabelle2[Date])*($B2=Tabelle2[employee]),0)),IF(AND(WEEKDAY($A2)=5,C$1="Thursday"),INDEX(Tabelle2[hours],MATCH(1,($A2=Tabelle2[Date])*($B2=Tabelle2[employee]),0)),IF(AND(WEEKDAY($A2)=6,C$1="Friday"),INDEX(Tabelle2[hours],MATCH(1,($A2=Tabelle2[Date])*($B2=Tabelle2[employee]),0)),IF(AND(WEEKDAY($A2)=7,C$1="Saturday"),INDEX(Tabelle2[hours],MATCH(1,($A2=Tabelle2[Date])*($B2=Tabelle2[employee]),0)),IF(AND(WEEKDAY($A2)=1,C$1="Sunday"),INDEX(Tabelle2[hours],MATCH(1,($A2=Tabelle2[Date])*($B2=Tabelle2[employee]),0)),"")))))))Do you want another sheet for employee number 1100? You can copy the original sheet for employee 1123 and then enter employee number 1100 in range B2:B57.
=IF(AND(WEEKDAY($A2)=2,C$1="Monday"),INDEX(Tabelle2[hours],MATCH(1,(Tabelle1!$A2=Tabelle2[Date])*(Tabelle1!$B2=Tabelle2[employee]),0)),IF(AND(WEEKDAY($A2)=3,C$1="Tuesday"),INDEX(Tabelle2[hours],MATCH(1,(Tabelle1!$A2=Tabelle2[Date])*(Tabelle1!$B2=Tabelle2[employee]),0)),IF(AND(WEEKDAY($A2)=4,C$1="Wednesday"),INDEX(Tabelle2[hours],MATCH(1,(Tabelle1!$A2=Tabelle2[Date])*(Tabelle1!$B2=Tabelle2[employee]),0)),IF(AND(WEEKDAY($A2)=5,C$1="Thursday"),INDEX(Tabelle2[hours],MATCH(1,(Tabelle1!$A2=Tabelle2[Date])*(Tabelle1!$B2=Tabelle2[employee]),0)),IF(AND(WEEKDAY($A2)=6,C$1="Friday"),INDEX(Tabelle2[hours],MATCH(1,(Tabelle1!$A2=Tabelle2[Date])*(Tabelle1!$B2=Tabelle2[employee]),0)),IF(AND(WEEKDAY($A2)=7,C$1="Saturday"),INDEX(Tabelle2[hours],MATCH(1,(Tabelle1!$A2=Tabelle2[Date])*(Tabelle1!$B2=Tabelle2[employee]),0)),IF(AND(WEEKDAY($A2)=1,C$1="Sunday"),INDEX(Tabelle2[hours],MATCH(1,(Tabelle1!$A2=Tabelle2[Date])*(Tabelle1!$B2=Tabelle2[employee]),0)),"")))))))You can try this formula. The formula must be entered with ctrl+shift+enter if one doesn't work with Office 365 or Excel 2021.
An alternative could be Power Query. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.
OliverScheurich I forgot to mention that both of these are on 2 separate worksheets within the excel, how do I word that formula to go between the 2 worksheets?
=IF(AND(WEEKDAY($A2)=2,C$1="Monday"),INDEX(Tabelle2[hours],MATCH(1,($A2=Tabelle2[Date])*($B2=Tabelle2[employee]),0)),IF(AND(WEEKDAY($A2)=3,C$1="Tuesday"),INDEX(Tabelle2[hours],MATCH(1,($A2=Tabelle2[Date])*($B2=Tabelle2[employee]),0)),IF(AND(WEEKDAY($A2)=4,C$1="Wednesday"),INDEX(Tabelle2[hours],MATCH(1,($A2=Tabelle2[Date])*($B2=Tabelle2[employee]),0)),IF(AND(WEEKDAY($A2)=5,C$1="Thursday"),INDEX(Tabelle2[hours],MATCH(1,($A2=Tabelle2[Date])*($B2=Tabelle2[employee]),0)),IF(AND(WEEKDAY($A2)=6,C$1="Friday"),INDEX(Tabelle2[hours],MATCH(1,($A2=Tabelle2[Date])*($B2=Tabelle2[employee]),0)),IF(AND(WEEKDAY($A2)=7,C$1="Saturday"),INDEX(Tabelle2[hours],MATCH(1,($A2=Tabelle2[Date])*($B2=Tabelle2[employee]),0)),IF(AND(WEEKDAY($A2)=1,C$1="Sunday"),INDEX(Tabelle2[hours],MATCH(1,($A2=Tabelle2[Date])*($B2=Tabelle2[employee]),0)),"")))))))Do you want another sheet for employee number 1100? You can copy the original sheet for employee 1123 and then enter employee number 1100 in range B2:B57.
- Thanks, that is sorted. MY requirements have changed. I need to go from April to March this year. April 1st was on a Friday. How do I rearrange this so it works from the start of April through to the end of march and how would I word the formula?
