3D Reference in Google Sheet

3D referenceing is a feature available in Microsoft Excel

A reference that refers to the same cell or range on multiple sheets is called a 3-D reference. A 3-D reference is useful and convenient way to reference several worksheets that follow the same pattern and cells on each worksheet contain the same type of data, such as when you consolidate budget data from different departments in your organization.

example

=SUM(Sales:Marketing!B3)

This feature is ***NOT AVAILABLE IN GOOGLE SHEET BY DEFAULT***

But often you may need such a feature.

This can be done with AppScripts embeded in the Google Sheet as mentioned here

Follow these steps to open the apps script code editor window.

1. Select the Extensions menu.
2. Click on the Apps Script option.
3. add the script below( a slightly changed version from that in the reference site)

After that, follow these steps to list all sheet names in Google Sheets.

1. Select any cell and type your header such as Sheet Names.
2. Select the cell below, in this example cell I1.

AND

3. Select the "Sheets" option in the menu that was added by onOpen() function your script.
4. Press List All Sheets option to trigger the listSheets() function.

//https://www.oksheets.com/list-sheet-names/
function listSheets() {
  var sheetNames = new Array();
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  var selectedSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var selectedRow = selectedSheet.getActiveCell().getRow();
  var selectedCol = selectedSheet.getActiveCell().getColumn();
  var range = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(selectedRow, selectedCol, (sheets.length +1), 2);
  for (var i = 0; i < sheets.length; i++) {
    sheetNames.push([sheets[i].getName(),"=" +  sheets[i].getName() + "!I1"]);
  }
  //sheetNames.push(["TOTAL",sumRows(sheets.length)]);
  let valuesRangeColLetter = columnToLetter(selectedSheet.getActiveCell().getColumn() +1);
  let valueRangeStartCell =  valuesRangeColLetter + selectedSheet.getActiveCell().getRowIndex();
  let valueRangeEndCell =   valuesRangeColLetter + (selectedSheet.getActiveCell().getRowIndex() + sheets.length - 1);
  let sumRange = "=SUM(" + valueRangeStartCell + ":" +  valueRangeEndCell + ")" ;
  sheetNames.push(["TOTAL",sumRange]);
  range.setValues(sheetNames);
}
function onOpen() {
  SpreadsheetApp.getUi()
      .createMenu('Sheets')
      .addItem('List All Sheets', 'listSheets')
      .addToUi();
}
//https://stackoverflow.com/a/21231012
function columnToLetter(column)
{
  var temp, letter = '';
  while (column > 0)
  {
    temp = (column - 1) % 26;
    letter = String.fromCharCode(temp + 65) + letter;
    column = (column - temp - 1) / 26;
  }
  return letter;
}

/**
 * This function will return sum of values of given number of rows (totRows) in the just prior column of the active cell from the current workbook.
 * @param {number} option , total rows to be summed.
 * @return sum of cell contents.
 * @customfunction
*/
function sumRows(totRows) {
  var sum = 0;
  var sh = SpreadsheetApp.getActiveSheet();
  var selectedSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var selectedRow = selectedSheet.getActiveCell().getRow();
  var selectedCol = selectedSheet.getActiveCell().getColumn();
  var values = sh.getRange(sh.getActiveCell().getRow(),sh.getActiveCell().getColumn()-1,totRows,1).getValues();

  for(var i in values){
    sum = sum + parseInt(values[i]);
  }
  return sum;
}
//https://www.oksheets.com/list-sheet-names/
/**
 * This function will return a list of sheet names from the current workbook.
 * @param {number} option Argument must be -1, 0, or 1.
 * @return A list of sheet names from the current workbook.
 * @customfunction
 */
function SHEETNAMES(option = 0) {

  var sheetNames = new Array()
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  var currSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();

  if (option === 0) {
    for (var i = 0; i < sheets.length; i++) sheetNames.push([sheets[i].getName()]);
  } else if (option === -1) {
    for (var i = 0; i < sheets.length; i++) if (currSheet != sheets[i].getName()) { sheetNames.push([sheets[i].getName()]) };
  } else if (option === 1) {
    sheetNames.push(currSheet);
  } else {
    throw "Option argument must be -1, 0, or 1";
  }
  return sheetNames
}