You can copy the VALUES (not formulas) from the "master" to the "slave", eliminating whole columns and/or specific rows based on values using PowerQuery. Then in the slave, you can right click and select "refresh" to import changes in the master to the
slave.
Does that describe what you want to do?
.
Here are some introductory articles about what PowerQuery can do for you.
If you provide us with a SMALL example file and instructions on what you want to change from the master to the slave we can help you generate the Query. At the bottom of this reply there is a link to a wiki on how to share files here, including a tool to
randomize values in an Excel file.
.
So what is Power Query?.
https://www.myexcelonline.com/blog/50-things-you-can-do-with-excel-power-query/ .
Well you know when you get data from a file, a data dump/import or an external data source and it comes into Excel in an ugly, unworkable format?
I can hear you say YEEEES!.
Well Power Query accesses, cleans and transforms all that messy data and displays it in a way that Excel loves and can work with.
The best thing is that the next time you receive the same ugly file, all you need to do is press the Refresh button and your data will automatically be imported, cleaned up and loaded into Excel so the data is displayed the “correct” way!.
So no more formulas, text to columns, trim spaces, vlookup, find & replace etc.
How to easily automate boring Excel tasks with Power Query!
2020 10 14
https://www.myonlinetraininghub.com/introduction-to-power-query
What’s the big deal about Power Query? Talk to those who have used it and they’ll tell you how amazing it is. Stories of automating tasks that used to take 3 hours now taking 3 minutes is not uncommon or an exaggeration.
If you haven’t heard of Excel’s Power Query tool, or you’ve heard of it but you’re not sure if it’ll be useful to you, then check out the video below where I showcase what the fuss is all about.
https://www.youtube.com/watch?v=L4BuUzccLpo&rel=0
17min
Power Query can automate the boring and laborious tasks of getting and cleaning data, reducing time spent on these tasks down to the click of a button!
00:29 How to get PowerQuery 2010-365, PowerBi
01:15 Why use PowerQuery (time saving)
02:10 Purpose of PowerQuery
02:35 Sources PowerQuery can get data from
03:23 Data Cleaning
03:43 Example 1: Get data from multiple files in a single folder - Intro
04:43 How to get data from a folder
05:50 Transform the data- intro to PQ user interface (starting with data in a single “sample” file)
06:48 Convert 2 row column headings into single row column headings
07:30 Split data in a column into multiple columns
08:28 Add a new column by multiplying 3 existing columns
09:25 Add a new column by example, removing honorifics from names
10:17 Add new column to calculate number of days from Order to Shipping
10:47 Filter data to remove some rows based on value(s)
11:34 Review recorded Query steps
11:45 Generalize the “sample file” query to apply to all of the files in the folder
12:10 Remove Source_name (file) column
12:18 Change column datatypes (not “formatting”) so Excel knows data types
13:22 Close and Load data directly to a PivotTable
14:22 Create a PivotTable
14:35 Auto Group Order Date
15:02 Create a Chart from the PivotTable
15:21 Get new data (new file) – Refresh All
.
Power Query Fundamentals Free 1.5hr course
https://www.powerquery.training/courses/power-query-fundamentals/
What you’ll learn
. * Basic understanding of what Power Query is and what it's designed for
. * How the Power Query User Interface is used to Extract, Transform and Load data
. * Simple transformation solutions that you can apply to your work
. * Building solutions to import data from common Data Sources
. * Methods to debug errors in your Power Query solutions
. * That you have been wasting a huge amount of time doing repetitive manual work that can easily be automated!
.**********************
******************
UPLOAD EXAMPLE - TROUBLE SHOOTING - SHARE PERSONAL ONEDRIVE FILE (NOT BUSINESS ONEDRIVE)
.
Trouble shooting problems in files can be like a visit to the dentist, a long, slow painful process of us trying to “extract” the clues needed to recognize the problem in a back and forth flow of questions and answers so that we can come
up with a / “the” solution.
.
Often it is faster and easier for everyone if we have a “sample file” get "hands on", to look at, and to “play with”.
.
This next link provides some tips on setting up a sample file and specific instructions for uploading and "sharing" it for us to access:
.
https://answers.microsoft.com/en-us/windows/forum/windows_other-winapps/trouble-shooting-share-onedrive-file/a231a097-bcbf-4e34-ad6c-a33118baf471
The article includes links to macros to randomize text in Word and numbers in Excel to preserve privacy
**************************************
.
This article describes another way of sharing Office files:
2020 03 09- Share a cloud stored document from Microsoft Office
https://office-watch.com/2020/share-cloud-stored-document-microsoft-office/
Using “Invites” to specific people from inside Office apps. Modify document access permissions and add a “note” with “how to” instructions for recipient
Share Tab of Backstage View in PowerPoint 365 for Windows
2020 10 12
https://www.indezine.com/products/powerpoint/learn/interface/365/share.html
Learn about the Share tab of Backstage view in PowerPoint 365 for Windows. You can share via OneDrive, PDF, or as an email attachment.
Applies to other Office 365 applications.
.
Video of what Excel example data could look like ****************
.
The first 2 minutes of this video gives an example of What I mean by a “simple” example. Use short simple names and quantities, and just 2 or 3 rows per sample data you want calculations done on.
You want simple numbers so you can do the math checks in your head.
Col- Stacking
Columns of Data (Unpivot, Split Columns by delimiter, and everything!)(PowerQuery)
2017 04 16
http://ozdusoleil.com/2017/04/16/power-query-stacking-columns-of-data-unpivot-split-columns-by-delimiter-and-everything/
.
**************************************
.
