To retrieve a cell value with a formula in Excel 2013, you can use an A1-style reference or a named reference. An A1-style reference describes the cell at the intersection of the column (A) and the row (1). You can also name a cell and then reference the cell by the name, which is helpful when retrieving cell values from different worksheets or workbooks.
Retrieve a Cell Value
Retrieving Values Using an A1-Style Reference
To retrieve a cell value from a different worksheet using an A1-style reference, type an equal sign to begin the formula and then navigate to the desired sheet by clicking the sheet's tab. Select the desired cell and Excel will insert the proper reference.
For example, if the desired cell value is stored in C12 on a sheet named Sheet 2, the resulting formula is ='Sheet 2'!C12.
Excel inserts the sheet name and the cell reference separated by an exclamation mark. If a sheet name includes a space, the sheet name must by enclosed by 'single quotes.' If a sheet name does not include a space, then single quotes are not required.
Retrieving Values Using a Named Reference
To retrieve a value from a different worksheet using a named reference, select the desired cell and then enter a friendly name into the Name Box, such as SalesTotal. After typing the name, be sure to press the Enter key on your keyboard. If you don't press the Enter key, and instead click a different cell or icon, Excel won't store the name.
Note: Excel has restrictions for names, for example, a name can't include spaces or conflict with an existing Excel name such as A1.
Write Formula Using a Named Reference
To write the formula that retrieves the desired cell value, enter an equal sign and then type the name. The resulting formula is =SalesTotal.
If you don't need a formula to dynamically retrieve the cell value, and instead just want to copy the current cell value, copy the cell and then paste values. Select the cell, copy it using "Ctrl-C," select the destination cell, and then paste the values using "Ctrl-Alt-V" to open the Paste Special dialog and selecting "Values").
You can create a formula that retrieves a value from any open workbook by typing an equal sign, navigating to the other workbook by selecting "Switch Windows" from the Windows group of the View menu, selecting the desired cell, and then pressing "Enter." The resulting reference includes the full path to the workbook, including the workbook name, along with the sheet and cell reference. While the external workbook is open, Excel displays the reference with the workbook name enclosed in square brackets, such as "='[Workbook.xlsx]Sheet 2'!C12." When the external workbook is closed, Excel displays the reference with the full path, such as "='C:\My Documents[Workbook.xlsx]Sheet 2'!C12." If the target file moves in relation to the source file, the reference will break.
In practice, it is best to avoid formulas that reference external workbooks -- however, when it is required, use a named reference rather than an A1-style reference. This practice helps Excel maintain the reference even when new rows or columns are inserted.