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
Highlighted
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
Highlighted
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.
Highlighted

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

Highlighted
I was assuming you need more than just one cell from those sheets :-)