I am setting up a roster/timesheet and linking data to clients , staff and hours. The source workbook has sheets named Wk 1 then Wk 2 Wk 3 etc. The formula in the destination workbook is =SUM(J7-'[Weekly Time sheets homemade.xlsx]Wk 2'!$W$6)+O6 then next cell down I have written =SUM(J8-'[Weekly Time sheets homemade.xlsx]Wk 3'!$W$6)+O7 As their are 52 weeks I would like to drag the formula down with the week numbers changing as well. The formula value !$W$6) needs to remain as is which it is doing. Any ideas?I am new would appreciate any help :)
If the formula in your destination workbook is in K7, try to enter this formula: =J7-indirect(“‘[weekly time sheets homemade.xlsx]wk&row()-5&’!$w$6”)+o6 I haven’t tested the foregoing formula because I’m replying via mobile phone. One thing i’m sure is that Excel will apply the appropriate case of the formula upon entry thereof. Stated differently, Excel will convert Indirect to INDIRECT, and so forth.