Dragging Formulas and shifting values

Brass Contributor

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 :)

3 Replies
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.

@Cambosity 

I simulated your workbooks in the attached files. The formula in [Destination.xlsx]Sheet1!K6, copied down rows, is: 

=J6-INDIRECT("'[Weekly Time sheets homemade.xlsx]Wk "&ROW()-5&"'!W6")+O5

I hope the foregoing formula returns your desired result. If not, please let me know. 

@Cambosity 

The source workbook failed to attach. Here it is.