Home

Linking cells/sheets from different workbooks

%3CLINGO-SUB%20id%3D%22lingo-sub-748340%22%20slang%3D%22en-US%22%3ELinking%20cells%2Fsheets%20from%20different%20workbooks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-748340%22%20slang%3D%22en-US%22%3E%3CP%3ENot%20an%20Excel%20expert!%3C%2FP%3E%3CP%3EI%20have%20four%20workbooks%20of%20multiple%20sheets%2C%20each%20with%20a%20summary%20as%20the%20first%20sheet%26nbsp%3B(drawing%20from%20other%20sheets)%3C%2FP%3E%3CP%3EI%20want%20to%20create%20a%20fifth%20workbook%20that%20is%20a%20summary%20of%20the%20summaries%2C%20drawing%20from%20each%20of%20the%20workbooks.%3C%2FP%3E%3CP%3EHow%20can%20I%20do%20this%20in%20Excel%20365%20for%20Mac%3F%3C%2FP%3E%3CP%3EI%20have%20tried%20using%20copy%2Fpaste%20to%20bring%20in%20the%20summary%20sheets%20in%20full%20but%20the%20calculation%20results%20(from%20the%20source%20summaries)%20have%20not%20come%20across.%3C%2FP%3E%3CP%3EResources%20online%20are%20referring%20to%20features%20I%20do%20not%20see%20on%20Mac%20version%20(%22get%20and%20transform%22%20etc.).%3C%2FP%3E%3CP%3EAny%20help%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-748340%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-748417%22%20slang%3D%22en-US%22%3ERe%3A%20Linking%20cells%2Fsheets%20from%20different%20workbooks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-748417%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F374273%22%20target%3D%22_blank%22%3E%40rebeccap2021%3C%2FA%3E%26nbsp%3BUnfortunately%2C%20Power%20Query%20(aka%20Get%20%26amp%3B%20Transform)%20isn't%20available%20on%20Mac%2C%20but%20you%20can%20use%20direct%20cell%20links.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20example%2C%20with%20a%20source%20workbook%20open%20(one%20with%20data%20you%20want%20to%20link%20to)%2C%20enter%20%3D%20in%20a%20cell%20in%20your%20destination%20workbook%2C%20switch%20to%20the%20source%20workbook%2C%20select%20the%20cell%20to%20link%2C%20and%20press%20enter.%20Excel%20will%20capture%20the%20file%20path%2Fcell%20address%20for%20you.%20Note%20that%20it%20will%20return%20the%20link%20in%20Absolute%20form%20(%3D%24A%241)%2C%20so%20you'll%20want%20to%20remove%20the%20%24's%2C%20then%20you%20can%20copy%2Fpaste%20that%20formula%20wherever%20you%20want.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHTH%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-751768%22%20slang%3D%22en-US%22%3ERe%3A%20Linking%20cells%2Fsheets%20from%20different%20workbooks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-751768%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F10827%22%20target%3D%22_blank%22%3E%40Smitty%20Smith%3C%2FA%3E%26nbsp%3Bthanks%20for%20this.%20Seems%20to%20be%20working%2C%20although%20I%20imagine%20it%20won't%20update%20if%20the%20source%20sheet%20is%20updated%3F%20Shame%20that%20the%20full%20functionality%20is%20not%20on%20the%20Mac%20version%20(hint%3F).%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-752481%22%20slang%3D%22en-US%22%3ERe%3A%20Linking%20cells%2Fsheets%20from%20different%20workbooks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-752481%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F374273%22%20target%3D%22_blank%22%3E%40rebeccap2021%3C%2FA%3E%26nbsp%3BIf%20both%20workbooks%20are%20open%2C%20then%20the%20values%20should%20update%20automatically.%20If%20they%20don't%2C%20you%20can%20always%20go%20to%20%3CSTRONG%3EData%20%26gt%3B%20Edit%20Links%20%26gt%3B%20Update.%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-796057%22%20slang%3D%22en-US%22%3ERe%3A%20Linking%20cells%2Fsheets%20from%20different%20workbooks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-796057%22%20slang%3D%22en-US%22%3E%3CP%3ECan%20I%20copy%20a%20spread%20sheet%20into%20a%20linked%20page%20so%20that%20values%20when%20changed%20anywhere%20in%20the%20original%20spread%20sheet%2C%20they%20are%20automatically%20and%20newly%20reflected%20in%20the%20second%20spread%20sheet%3F%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F10827%22%20target%3D%22_blank%22%3E%40Smitty%20Smith%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-796060%22%20slang%3D%22en-US%22%3ERe%3A%20Linking%20cells%2Fsheets%20from%20different%20workbooks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-796060%22%20slang%3D%22en-US%22%3E%3CP%3EBut%20when%20I%20try%20to%20copy%20the%20original%20spread%20sheet%20my%20calculated%20values%20do%20not%20come%20through%2C%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F10827%22%20target%3D%22_blank%22%3E%40Smitty%20Smith%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-796069%22%20slang%3D%22en-US%22%3ERe%3A%20Linking%20cells%2Fsheets%20from%20different%20workbooks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-796069%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F389482%22%20target%3D%22_blank%22%3E%40BRusso26%3C%2FA%3E%26nbsp%3BSure%2C%20go%20to%20Copy%20%26gt%3B%20Paste%20Special%20%26gt%3B%20Paste%20Link.%20You'll%20want%20to%20watch%20out%20for%20unnecessary%20blank%20cells%2C%20where%20you'll%20probably%20want%20to%20delete%20the%20links%20for%20overhead.%3C%2FP%3E%3C%2FLINGO-BODY%3E
rebeccap2021
New Contributor

Not an Excel expert!

I have four workbooks of multiple sheets, each with a summary as the first sheet (drawing from other sheets)

I want to create a fifth workbook that is a summary of the summaries, drawing from each of the workbooks.

How can I do this in Excel 365 for Mac?

I have tried using copy/paste to bring in the summary sheets in full but the calculation results (from the source summaries) have not come across.

Resources online are referring to features I do not see on Mac version ("get and transform" etc.).

Any help appreciated.

6 Replies

@rebeccap2021 Unfortunately, Power Query (aka Get & Transform) isn't available on Mac, but you can use direct cell links.

 

For example, with a source workbook open (one with data you want to link to), enter = in a cell in your destination workbook, switch to the source workbook, select the cell to link, and press enter. Excel will capture the file path/cell address for you. Note that it will return the link in Absolute form (=$A$1), so you'll want to remove the $'s, then you can copy/paste that formula wherever you want.

 

HTH

 

 

@Smitty Smith thanks for this. Seems to be working, although I imagine it won't update if the source sheet is updated? Shame that the full functionality is not on the Mac version (hint?).

@rebeccap2021 If both workbooks are open, then the values should update automatically. If they don't, you can always go to Data > Edit Links > Update.

Can I copy a spread sheet into a linked page so that values when changed anywhere in the original spread sheet, they are automatically and newly reflected in the second spread sheet?@Smitty Smith 

But when I try to copy the original spread sheet my calculated values do not come through,

@Smitty Smith 

@BRusso26 Sure, go to Copy > Paste Special > Paste Link. You'll want to watch out for unnecessary blank cells, where you'll probably want to delete the links for overhead.

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