Discussion Selecting values that change while dragging formula. in Excel
https://techcommunity.microsoft.com/t5/excel/selecting-values-that-change-while-dragging-formula/m-p/615768#M34757
<P>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 <SPAN>!$W$6)</SPAN> needs to remain as is which it is doing. Any ideas?</P>Mon, 20 May 2019 15:13:16 GMTCambosity2019-05-20T15:13:16ZSelecting values that change while dragging formula.
https://techcommunity.microsoft.com/t5/excel/selecting-values-that-change-while-dragging-formula/m-p/615768#M34757
<P>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 <SPAN>!$W$6)</SPAN> needs to remain as is which it is doing. Any ideas?</P>Mon, 20 May 2019 15:13:16 GMThttps://techcommunity.microsoft.com/t5/excel/selecting-values-that-change-while-dragging-formula/m-p/615768#M34757Cambosity2019-05-20T15:13:16ZRe: Selecting values that change while dragging formula.
https://techcommunity.microsoft.com/t5/excel/selecting-values-that-change-while-dragging-formula/m-p/675937#M34758
<P><LI-USER uid="345048"></LI-USER> hello there. External links are <STRONG>HIGHLY</STRONG> 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.</P><P> </P><P>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:<BR />=SUM(J7-IFERROR(INDIRECT("'[Weekly Time sheets homemade.xlsx]"&"Wk "&ROW(A2)&"'!$W$6"),0))+O7</P><P> </P><P>Note this formula will fail if the workbook is closed, otherwise you'd need the whole file path instead of just the workbook name.</P><P> </P><P>Again, I wouldn't recommend the above as a solution, but assuming I understood you correctly, it would work.</P><P> </P><P>HTH</P>Thu, 06 Jun 2019 18:48:44 GMThttps://techcommunity.microsoft.com/t5/excel/selecting-values-that-change-while-dragging-formula/m-p/675937#M34758Zack Barresse2019-06-06T18:48:44Z