Automatic Data Sharing Across Sheets With IMPORTRANGE and Named Functions

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.)

This post showcases a unique use case for Named Functions and IMPORTRANGE.

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).

Click Next

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.

Pro Tip

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):

1. Go to Data > Named Functions
2. Click Import Function
3. Find the Parent Sheet file from the file picker, select it, and click Insert
4. Select the COMPANY_DETAILS function and click Import
5. Use this named function like any other function in your Sheet
6. Note: You will need to click Allow Access the first time you use it