Selecting values that change while dragging formula.

[Cambosity]

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?

[#2]

@Cambosity hello there. External links are HIGHLY not recommended. They are very problematic and, as you can tell already, difficult to deal with. I also understand some people who use spreadsheets do not have total control over what they are using, so what I say next should be taken in the context of, "there is no other way around it." What I would do is do a query to bring in the workbook data into a consolidated table, which you could then reference. This would essentially keep a layer between your formulas and external data, without the need for external formula references. Letting a query handle the external data is preferred.

However, if you want to use a formula, you'd want to look at the INDIRECT function. Assuming you wanted to start with "Wk 2" and move on to "Wk 3", "Wk 4", etc., you could use:
=SUM(J7-IFERROR(INDIRECT("'[Weekly Time sheets homemade.xlsx]"&"Wk "&ROW(A2)&"'!$W$6"),0))+O7

Note this formula will fail if the workbook is closed, otherwise you'd need the whole file path instead of just the workbook name.

Again, I wouldn't recommend the above as a solution, but assuming I understood you correctly, it would work.

HTH