Home

Selecting values that change while dragging formula.

%3CLINGO-SUB%20id%3D%22lingo-sub-615768%22%20slang%3D%22en-US%22%3ESelecting%20values%20that%20change%20while%20dragging%20formula.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-615768%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20setting%20up%20a%20roster%2Ftimesheet%20and%20linking%20data%20to%20clients%20%2C%20staff%20and%20hours.%20The%20source%20workbook%20has%20sheets%20named%20Wk%201%20then%20Wk%202%20Wk%203%20etc.%20The%20formula%20in%20the%20destination%20workbook%20is%26nbsp%3B%3DSUM(J7-'%5BWeekly%20Time%20sheets%20homemade.xlsx%5DWk%202'!%24W%246)%2BO6%26nbsp%3B%20then%20next%20cell%20down%20I%20have%20written%26nbsp%3B%3DSUM(J8-'%5BWeekly%20Time%20sheets%20homemade.xlsx%5DWk%203'!%24W%246)%2BO7%26nbsp%3B%20As%20their%20are%2052%20weeks%20I%20would%20like%20to%20drag%20the%20formula%20down%20with%20the%20week%20numbers%20changing%20as%20well.%20The%20formula%20value%26nbsp%3B%3CSPAN%3E!%24W%246)%3C%2FSPAN%3E%20needs%20to%20remain%20as%20is%20which%20it%20is%20doing.%20Any%20ideas%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-615768%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20Desktop%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ENeed%20Help%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-675937%22%20slang%3D%22en-US%22%3ERe%3A%20Selecting%20values%20that%20change%20while%20dragging%20formula.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-675937%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F345048%22%20target%3D%22_blank%22%3E%40Cambosity%3C%2FA%3E%26nbsp%3Bhello%20there.%20External%20links%20are%20%3CSTRONG%3EHIGHLY%3C%2FSTRONG%3E%20not%20recommended.%20They%20are%20very%20problematic%20and%2C%20as%20you%20can%20tell%20already%2C%20difficult%20to%20deal%20with.%20I%20also%20understand%20some%20people%20who%20use%20spreadsheets%20do%20not%20have%20total%20control%20over%20what%20they%20are%20using%2C%20so%20what%20I%20say%20next%20should%20be%20taken%20in%20the%20context%20of%2C%20%22there%20is%20no%20other%20way%20around%20it.%22%20What%20I%20would%20do%20is%20do%20a%20query%20to%20bring%20in%20the%20workbook%20data%20into%20a%20consolidated%20table%2C%20which%20you%20could%20then%20reference.%20This%20would%20essentially%20keep%20a%20layer%20between%20your%20formulas%20and%20external%20data%2C%20without%20the%20need%20for%20external%20formula%20references.%20Letting%20a%20query%20handle%20the%20external%20data%20is%20preferred.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20if%20you%20want%20to%20use%20a%20formula%2C%20you'd%20want%20to%20look%20at%20the%20INDIRECT%20function.%20Assuming%20you%20wanted%20to%20start%20with%20%22Wk%202%22%20and%20move%20on%20to%20%22Wk%203%22%2C%20%22Wk%204%22%2C%20etc.%2C%20you%20could%20use%3A%3CBR%20%2F%3E%3DSUM(J7-IFERROR(INDIRECT(%22'%5BWeekly%20Time%20sheets%20homemade.xlsx%5D%22%26amp%3B%22Wk%20%22%26amp%3BROW(A2)%26amp%3B%22'!%24W%246%22)%2C0))%2BO7%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENote%20this%20formula%20will%20fail%20if%20the%20workbook%20is%20closed%2C%20otherwise%20you'd%20need%20the%20whole%20file%20path%20instead%20of%20just%20the%20workbook%20name.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAgain%2C%20I%20wouldn't%20recommend%20the%20above%20as%20a%20solution%2C%20but%20assuming%20I%20understood%20you%20correctly%2C%20it%20would%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHTH%3C%2FP%3E%3C%2FLINGO-BODY%3E
Cambosity
New 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?

1 Reply

@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

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies