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:

• start - The name of the sheet to start the reference at.
• end - The name of the sheet to end the reference at.
• range - The range to return from each sheet, as a string.

Advanced

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

• prefix - In most cases this is the string preceding a number in sheet names. If regex is set to true then this is the regular expression that sheet names should match against. For a sheet named “List 1”, the prefix is “List ” (with the space).
• regex - Boolean. When set to true, the function will ignore the startValue, step, endValue & suffix parameters. Ranges will be returned from sheets where the name matches the regular expression in the prefix parameter. Default: FALSE.
• range - The range you want values to be returned from. This will be the same across all sheets and not include the sheet name. Eg. “A1:B5”.
• startValue - The sheet number to start at when iterating. For example, If you wanted to start returning values from a sheet named “List 2” then you would enter “2” here.
• step - The step which sheets will be iterated through. If you want to return every second sheet then you would enter “2”. Must be an integer greater than or equal to 1. Default: 1.
• endValue - The sheet number to end at when iterating. For example, If you wanted to stop returning values at a sheet named “List 5” then you would enter “5” here.
• suffix - The string proceeding a number in sheet names. For a sheet named “List 5 redacted”, the suffix is ” redacted” (with the space).
• includeSheetName - Optional. Include the name of each sheet in the first column of the data. Default: False

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

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:

• View and manage the files in your Google Drive & View and manage your spreadsheets in Google Drive - Used to read the value of cells in the active document.
• Display and run third-party web content in prompts and sidebars inside Google applications​ - Used to provide a fancy user interface.

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.