This automatic data sharing technique first appeared in my weekly Google Sheets Tip newsletter #269, August 2023.
(Don’t miss out! Join today to get future editions.)
Automatic Data Sharing Scenario
Suppose you want to include a standardized piece of text on the top of your Sheets, e.g. a legal disclaimer or perhaps your company contact details. Sort of like an email signature, but for your Sheet.
There’s a clever trick you can do with Named Functions and IMPORTRANGE to automate this across all your Sheets.
Best of all, if the information in the parent sheet is updated, these changes will flow through to any child sheet.
In this GIF, when we update the address in the parent sheet, the change automatically flows through to the child sheet:
Of course, you could skip this named function and just add the plain IMPORTRANGE function, but this requires you to write out that function and know the range reference.
I think this named function method simplifies that, especially for non-technical users.
How To Setup Automatic Data Sharing
Start in the Parent Sheet, i.e. the Sheet that will be the source of truth for your information.
Create a new named function: Data > Named functions
Then select: Add new function at the bottom of the sidebar.
Call the new Named Function COMPANY_DETAILS.
Add a description like “Insert company details”.
Define it as follows in the Formula definition box:
The URL inside the IMPORTRANGE should be the URL of the Parent Sheet you’re in, i.e. the Sheet where you’re defining the function.
The range reference (Sheet1!B3:B5 in my case) should be the range containing your company information (e.g. contact details).
On the following page, click Create
Back in your Sheet, you can now insert this snippet of text anywhere you like using this function:
This is cool but not particularly useful yet.
Use a named range (e.g. “companyDetails”) inside the IMPORTRANGE function instead of a static range reference (e.g. “Sheet1!B3:B5”). That way, if you ever move the data in the parent sheet, or insert/delete rows, the reference inside the named function won’t break.
Read more in Google Sheets Tip #169.
Using This Named Function In Other Sheets
When you import this Named Function into other Sheets, you can enjoy automatic data sharing from the parent sheet.
If you change the text in the parent Sheet, it will propagate through to the child Sheet because of the IMPORTRANGE function inside the Named Function.
This is a great boon for ensuring consistency between your Sheets. You only ever need to change the data in the parent Sheet.
Set Up Steps
In the child sheet where you want to add the information (e.g. a client report):
- Go to Data > Named Functions
- Click Import Function
- Find the Parent Sheet file from the file picker, select it, and click Insert
- Select the COMPANY_DETAILS function and click Import
- Use this named function like any other function in your Sheet
- Note: You will need to click Allow Access the first time you use it