3D Reference#

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

Google Workspace Marketplace

apps-script icon google-sheets icon javascript icon

3D Reference

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
Menu Option
Replace Functions GUI Modal
GUI

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:

Uh-oh, Something Broke!

Contact me, giving as much detail as you can.

Changelog