Need help on what formulas to use to able to complete a table

[alexjw94]

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?

[#2]

@alexjw94 

=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.

[#3]

@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?

[#4 · Accepted solution]

@alexjw94 

=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.

[#5]

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?

[#6]

@alexjw94 

Can you attach screenshots without sensitive data which show the layout of the data along with the expected result?

[#7]

I have managed to sort the issue, thanks @OliverScheurich