Forum Discussion
Not carrying formatting over
I wonder if someone can solve this one for me...
I have multiple workbooks, each with summary sheets. Someone here kindly helped me make a formula so I can mirror all the summary sheets into one workbook. The only problem is that the summary pages are all formatted perfectly but when I use the below formula it doesn't carry of this across.
Is there a way that I can do this? I've pasted the formula below
=LET(range,'https://retailstarbucks1com.sharepoint.com/sites/M23Corridor/Shared Documents/General/Labour/[Purley.xlsx]Quarterly Summary'!A1:Q31,IF(range="", "", range))
Thank you!!
2 Replies
PowerQuery is the best option to fetch live data from an external workbook. The limitation of dynamic arrays is you must have the external workbook open.
The formula you have provided is using Excel's LET function and external references to pull data from another workbook (Purley.xlsx), but as you mentioned, formatting is not carried over when you do this.
Formulas like this one will only bring in the values of the cells, not the formatting (colors, fonts, borders, etc.). Excel does not support the automatic transfer of cell formatting through external formulas.
You have a few options to copy both data and formatting.
VBA Automation to Copy Data and Formatting.
Power Query for Data, VBA for Formatting.
Linking with Power Automate Desktop (Advanced).
Manual Copy-Paste with Formatting (Not Automated).
For Manual Use: Paste Special works, but you'll need to do this frequently.
For Automation: VBA is the best option for copying both values and formats.
For Data Import (without formats): Power Query is great for pulling the data in, but you'd need to use VBA for formatting.
Hope it helps you
