Forum Discussion
Need help on what formulas to use to able to complete a table
Hi,
So i have got some data in the below screenshot that I am wanting to convert to a blank purpose made timesheet (2nd screenshot) in excel.
The first blanksheet I have got set up with the first employee no "1123". How can I get this to look up the date and input the total no of hours to quantify each cell Monday to Friday by going of the W/C date column?
=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.
