Jul 10 2019 09:49 AM
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.
Jul 10 2019 10:11 AM
@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
Jul 12 2019 01:30 AM
@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?).
Jul 12 2019 07:55 AM
@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.
Aug 08 2019 01:33 PM
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
Aug 08 2019 01:34 PM
But when I try to copy the original spread sheet my calculated values do not come through,
Aug 08 2019 01:45 PM
@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.