3D Reference

Output a 2D array containing values from a common range across multiple sheets.

ongoing since google sheets tagJavaScript tag Google Workspace Marketplace

Description

Output a 2D array containing values from a common range across multiple sheets. Use a basic function or configure the 3D reference with regex.

How To Use

Basic

Enter ‘=DDDREF()’ in any cell. The parameters for this function are listed below:

Advanced

Enter ‘=DDDREF_ADV()’ in any cell. The parameters for this function are listed below:

’Replace Functions’ Menu

Due to the way Google Sheets add-ons are implemented, it isn’t possible to listen to the 3D references in real-time (I imagine they’d be built-in otherwise). This means that only changes to the input parameters for DDDREF and DDDREF_ADV will cause an update, as well as periodic re-loading. To get around this, you can go to Add-ons > 3D Reference > Replace Functions and enter ranges containing 3D references. The add-on will then replace the DDDREF function with a native Google Sheets alternative that can update in real-time. This is best used when you know you will not need to change which cells the 3D reference refers to. It does not work for DDDREF_ADV.

Find Replace Functions Options

Replace Functions GUI Modal

If you are unable to replace functions, you may benefit from updating the documents recalculation frequency:

  1. Click File.
  2. Select Spreadsheet settings.
  3. Choose the Calculation tab.
  4. Click the Recalculation drop down.
  5. Select the desired update frequency.
  6. Click Save Settings.

Permissions

Here’s what 3D Reference does with each of the permissions it requires:

Further details can be found in the GSuite Add-on Privacy Policy.

Uh-oh, Something Broke!

Contact me, giving as much detail as you can.