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
Highlighted
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
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies