I couldn’t find an overview of how address (references) are constructed in Excel, so here is mine.
Simplest case: Referencing cells on the same worksheet
Note added 2016-08-18: Rectangle addresses are ‘normalized’ so that the top-left cell comes in front of the colon and the bottom-right cell after. For example,
C1:A3is normalized to
Referencing cells on different worksheet
Reminder: Worksheet names must have 1-31 characters and must not contain
: \ / ? * [ ].
=sheetname!A1:D4 ='sheet name with spaces'!A1:D4
A hyphen requires quotation…
… but an underscore does not:
Umlauts do not require notation either:
Note added 2016-08-18: In summary, if the worksheet name contains a character that is not an alphanumeric character or an underscore, it must be quoted.
Referencing cells in different workbook
If the worksheet has characters in its name that require quotation, the workbook and sheet name part are quoted together:
='[workbook.xlsx]sheet name with spaces'!A1:D4
The same holds true for workbook names with characters that need escaping, such as spaces:
='[workbook with spaces.xlsx]Sheet1'!A1:D4
Again, umlauts do not require escaping:
Hyphen requires escaping:
… but underscore does not:
Referencing workbook in another folder/on a different drive
Things get interesting if the referencing and the referenced workbook reside in different folders and/or different drives.
As long as both workbooks are open, the address does not include the drive/folder part:
c:\referencing_workbook.xlsx x:\my_folder\referenced_workbook.xlsx =[referenced_workbook.xlsx]Sheet1!A1:A4
Once the referenced workbook is closed however, the drive and folder path are included in the reference:
Note how the drive and path are not enclosed by square brackets! It is
just the workbook name that is enclosed by
If the reference needs to be quoted due to special characters, everything up to the exclamation point is quoted:
That’s about it.