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

=A1
=A1:D4

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:A3 is normalized to A1:C3.

Referencing cells on different worksheet

Reminder: Worksheet names must have 1-31 characters and must not contain any of : \ / ? * [ ].

=sheetname!A1:D4
='sheet name with spaces'!A1:D4

A hyphen requires quotation…

='sheet-with-hyphens'!A1:D4

… but an underscore does not:

=sheet_with_underscores!A1:D4

Umlauts do not require notation either:

=Hällo!A1:D4

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

=[workbook.xlsx]sheetname!A1:D4

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:

=[wörkböök.xlsx]Sheet1!A1:D4

Hyphen requires escaping:

='[work-book.xlsx]Sheet1!A1:D4

… but underscore does not:

=[work_book.xlsx]Sheet1!A1:D4

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:

=x:\my_folder\[referenced_workbook.xlsx]Sheet1!A1:A4

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:

='x:\my_folder\[referenced_workbook.xlsx]Sheet1'!A1:A4

That’s about it.

Blog index

Atom newsfeed

Comments

Post date

Sun 17 May 2015

Tags

Share

Recent posts

Exit ThinkPad T430s, enter ThinkPad T480s

Linux and VirtualBox on a T480s with high-resolution display

What I like and dislike about Ubuntu 18.04

Excel Blogs