You may often refer to data from other sheets in Google Spreadsheet or Excel. In such cases, here is how to reference the sheet name from the cell value.
Normal other sheet references
If you want to refer to data on another sheet, you can use input methods such as “‘sheet name’! cell number” or similar input method to display the results.
='シート 1'!A1
The values in cell A1 of the tally sheet are now displayed.
Trying to cell reference a sheet name…
The problem here is when you want to “refer to the value of another cell as the sheet name.
In this case, an error is displayed if you try to concatenate them by enclosing them in single or double quotation marks.
Unable to recognize sheet name.
The INDIRECT function can be used here.
Using the INDIRECT function
The INDIRECT function can be used to get the sheet name by reference to a cell.
INDIRECT
https://support.google.com/docs/answer/3093377?hl=ja
Returns a cell reference specified by a string.
Specify the cells you want to designate and join them with & (ampersand), followed by ! Cell Name”. The “! Cell name” is enclosed in “(double quotation marks)”.
The results are displayed!
summary
The INDIRECT function can be used to refer to a sheet by reference to a cell value.
This is useful when you want to refer to a sheet at once, such as with auto-fill, so it is definitely worth keeping in mind.
Please refer to 😃.