Home

Please help! Cell Reference To A Shared Workbook

%3CLINGO-SUB%20id%3D%22lingo-sub-550883%22%20slang%3D%22en-US%22%3EPlease%20help!%20Cell%20Reference%20To%20A%20Shared%20Workbook%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-550883%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20workbook%20with%20a%20list%20of%20locations.%20I%20parse%20out%20shared%20workbooks%20to%20respective%20regional%20managers.%20The%20regional%20managers%20enter%20contractor%20information%20and%20hours%20worked%20at%20each%20location.%3CBR%20%2F%3EI%20want%20to%20reference%20their%20hours%20for%20each%20day%20into%20my%20master%20sheet.%3CBR%20%2F%3E%3CBR%20%2F%3EThe%20problem%20arises%20when%20the%20regional%20managers%20sort%20their%20lists%20by%20some%20metric%20-%20say%20zip%20code.%26nbsp%3B%20The%20data%20source%20location%20changes%20and%20my%20reference%20formula%20only%20targets%20the%20cell.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EIs%20there%20a%20way%20for%20my%20reference%20formula%20to%20%22follow%22%20the%20data%3F%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E%3D'%5BRegional%20Store%20Locations.xlsx%5DSheet1'!A1%26nbsp%3B%20is%20the%20reference%20formula%20im%20trying%20to%20manipulate%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-550883%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-551065%22%20slang%3D%22en-US%22%3ERe%3A%20Please%20help!%20Cell%20Reference%20To%20A%20Shared%20Workbook%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-551065%22%20slang%3D%22en-US%22%3EYou%20could%20pull%20in%20the%20data%20from%20the%20workbooks%20using%20e.g.%20Data%2C%20New%20Query%20(or%20get%20data)%2C%20From%20File%2C%20From%20Folder.%20Then%20once%20you%20have%20all%20information%20in%20one%20table%2C%20reporting%20gets%20easier.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-551091%22%20slang%3D%22en-US%22%3ERe%3A%20Please%20help!%20Cell%20Reference%20To%20A%20Shared%20Workbook%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-551091%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%2C%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%26nbsp%3B.%20How%20do%20I%20Get%20Data%20from%20a%20cell%20as%20opposed%20to%20a%20sheet%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-553421%22%20slang%3D%22en-US%22%3ERe%3A%20Please%20help!%20Cell%20Reference%20To%20A%20Shared%20Workbook%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-553421%22%20slang%3D%22en-US%22%3EI%20was%20assuming%20you%20need%20more%20than%20just%20one%20cell%20from%20those%20sheets%20%3A-)%3C%2FLINGO-BODY%3E
eastside235
New Contributor

I have a workbook with a list of locations. I parse out shared workbooks to respective regional managers. The regional managers enter contractor information and hours worked at each location.
I want to reference their hours for each day into my master sheet.

The problem arises when the regional managers sort their lists by some metric - say zip code.  The data source location changes and my reference formula only targets the cell. 

Is there a way for my reference formula to "follow" the data?


='[Regional Store Locations.xlsx]Sheet1'!A1  is the reference formula im trying to manipulate

3 Replies
You could pull in the data from the workbooks using e.g. Data, New Query (or get data), From File, From Folder. Then once you have all information in one table, reporting gets easier.

Thanks, @Jan Karel Pieterse . How do I Get Data from a cell as opposed to a sheet?

I was assuming you need more than just one cell from those sheets :-)
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
21 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
12 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies