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.