This short tutorial explains the basics of an external reference in Excel, and shows how to reference another sheet and workbook in your formulas.
When calculating data in Excel, you may often find yourself in a situation when you need to pull data from another worksheet or even from a different Excel file. Can you do that? Of course, you can. You just need to create a link between the worksheets (within the same workbook or in different workbooks) by using what is called an external cell reference or a link.
An external cell reference in Excel is nothing more than a reference to a cell or a range of cells outside the current worksheet. The main benefit of using an Excel external reference is that whenever the referenced cell(s) in another worksheet changes, the value returned by the external cell reference is automatically updated.
Although external references in Excel are very similar to cell references, there are a few important differences. In this tutorial, we'll start with the basics and show how to create various external reference types with detailed steps, screenshots and formula examples.
How to reference another sheet in Excel
To reference a cell or range of cells in another worksheet in the same workbook, put the worksheet name followed by an exclamation mark (!) before the cell address.
In other words, in an Excel reference to another worksheet, you use the following format:
Reference to an individual cell:
For example, to refer to cell A1 in Sheet2, you type Sheet2!A1.
Reference to a range of cells:
Sheet_name!First_cell:Last_cell
For example, to refer to cells A1:A10 in Sheet2, you type Sheet2!A1:A10.
Note. If the worksheet name includes spaces or non-alphabetical characters, you must enclose it in single quotation marks. For example, an external reference to cell A1 in a worksheet named Project Milestones should read as follows: 'Project Milestones'!A1.
In a real-life formula, which multiplies the value in cell A1 in 'Project Milestones' sheet by 10, an Excel sheet reference looks like this:
='Project Milestones'!A1*10
Creating a reference to another sheet in Excel
When writing a formula that refers to cells in another worksheet, you can of course type that other sheet name followed by an exclamation point and a cell reference manually, but this would be a slow and error-prone way.
A better way is point to the cell(s) in another sheet that you want the formula to refer to, and let Excel take care of the correct syntax of your sheet reference. To have Excel insert a reference to another sheet in your formula, do the following:
- Start typing a formula either in a destination cell or in the formula bar.
- When it comes to adding a reference to another worksheet, switch to that sheet and select a cell or a range of cells you want to refer to.
- Finish typing the formula and press the Enter key to complete it.
For example, if you have a list of sales figures in sheet Sales and you want to calculate the Value Added Tax (19%) for each product in another sheet named VAT, proceed in the following way:
- Start typing the formula =19%* in cell B2 on sheet VAT.
- Switch to sheet Sales, and click on cell B2 there. Excel will immediately insert an external reference to that cell, as shown in the following screenshot:
- Press Enter to complete the formula.
Note. When adding an Excel reference to another sheet using the above method, by default Microsoft Excel adds a relative reference (with no $ sign). So, in the above example, you can just copy the formula to other cells in column B on sheet VAT, the cell references will adjust for each row, and you will have VAT for each product correctly calculated.
In a similar manner, you can reference arange of cells in another sheet. The only difference is that you select multiple cells on the source worksheet. For example, to find out the total of sales in cells B2:B5 on sheet Sales, you would enter the following formula:
=SUM(Sales!B2:B5)
This is how you reference another sheet in Excel. And now, let's see how you can refer to cells from a different workbook.
How to reference another workbook in Excel
In Microsoft Excel formulas, external references to another workbook are displayed in two ways, depending on whether the source workbook is open or closed.
External reference to an open workbook
When the source workbook is open, an Excel external reference includes the workbook name in square brackets (including the file extension), followed by the sheet name, exclamation point (!), and the referenced cell or a range of cells. In other words, you use the following reference format for an open workbook reference: Crypt of the necrodancer download.
For example, here's an external reference to cells B2:B5 on sheet Jan in the workbook named Sales.xlsx:
[Sales.xlsx]Jan!B2:B5
If you want, say, to calculate the sum of those cells, the formula with the workbook reference would look as follows:
=SUM([Sales.xlsx]Jan!B2:B5)
External reference to a closed workbook
When you reference another workbook in Excel, that other workbook does not necessarily need to be open. If the source workbook is closed, you must add the entire path to your external reference.
For example, to add up cells B2:B5 in the Jan sheet from Sales.xlsx workbook that resides within the Reports folder on drive D, you write the following formula:
=SUM(D:Reports[Sales.xlsx]Jan!B2:B5)
Here's a breakdown of the reference parts:
- File Path. It points to the drive and directory in which your Excel file is stored (D:Reports in this example).
- Workbook Name. It includes the file extension (.xlsx, .xls, or .xslm) and is always enclosed in square brackets, like [Sales.xlsx] in the above formula.
- Sheet Name. This part of the Excel external reference includes the sheet name followed by an exclamation point where the referenced cell(s) is located (Jan! in this example).
- Cell Reference. It points to the actual cell or a range of cells referenced in your formula.
If you've created an reference to another workbook when that workbook was open, and after that you closed the source workbook, your external workbook reference will get updated automatically to include the entire path.
Note. If either the workbook name or sheet name, or both, include spaces or any non-alphabetical characters, you must enclose the name or path in single quotation marks. For example:=SUM('[Year budget.xlsx]Jan'!B2:B5)
=SUM('[Sales.xlsx]Jan sales'!B2:B5)
=SUM('D:Reports[Sales.xlsx]Jan sales'!B2:B5)
Making a reference to another workbook in Excel
As is the case with creating an Excel formula that references another sheet, you don't have to type a reference to a different workbook manually. Just switch to the other workbook when entering your formula, and select a cell or a range of cells you want to refer to. Microsoft Excel will take care of the rest:
Note. When creating a reference to another workbook by selecting the cell(s) in it, Excel always inserts absolute cell references. Therefore, if you intend to copy the newly created formula to other cells, be sure to remove the dollar sign ($) from the cell references to turn them into relative or mixed references, depending on your purposes.
Reference to a defined name in the same or another workbook
To make an Excel external reference more compact, you can create a defined name in the source sheet, and then refer to that name from another sheet that resides in the same workbook or in a different workbook.
Creating a name in Excel
To create a name in Excel, select all the cells you want to include, and then either go to the Formulas tab > Defined names group and click the Define name button, or press Ctrl + F3 and click New.
In the New Name dialog, type any name you want (remember that spaces are not allowed in Excel names), and check if the correct range is displayed in the Refers to field.
For example, this is how we create a name (Jan_sales) for cells B2:B5 in Jan sheet:
Once the name is created, you are free to use it in your external references in Excel. The format of such references is much simpler than the format of an Excel sheet reference and workbook reference discussed earlier, which makes the formulas with name references easier to comprehend.
Note. By default, Excel names are created for the workbook level, please notice the Scope field in the screenshot above. But you can also make a specific worksheet level name by choosing a corresponding sheet from the Scope drop-down list. For Excel references, the scope of a name is very important because it determines the location within which the name is recognized.
It's recommended that you always create workbook-level names (unless you have a specific reason not to), because they significantly simplify creating Excel external references, as illustrated in the following examples.
I may access OPC server on local host. How to add point in matricon opc. I have similar problem. On computer with OPC server (remote computer)you have to configure:run:dcomcnfg - mmc for dcom interfaceI)My computer - Properties- DefaultProperties1) check Enable distributed COM on this computer2) default authentification level - None3) default impersonation lever - impersonateII)- COM Security1) Access permissions - Edit limits - add 'guest' and enable all rights2) Launch and activation permissions - Edit limits - add 'guest' and enable all rightsThat's allin local Event Viewer (where opc client is running) monitor what is happening.
Referencing a name in another sheet in the same workbook
To reference a global workbook-level name in the same workbook, you simply type that name in a function's argument:
For example, to find the sum of all the cells within the Jan_sales name that we created a moment ago, use the following formula:
=SUM(Jan_sales)
To reference a local worksheet-level name in another sheet within the same workbook, you need to precede the name with the sheet name followed by an exclamation mark:
=Function(Sheet_name!name)
For example:
=SUM(Jan!Jan_sales)
If the sheet names includes spaces or mon-alphabetic chars, remember to enclose it in single quotes, e.g.:
=SUM('Jan report'!Jan_Sales)
Referencing a name in another workbook
A reference to a workbook-level name in a different workbook consists of the workbook name (including the extension) followed by an exclamation point, and the defined name (named range):
=Function(Workbook_name!name)
For example:
=SUM(Sales.xlsx!Jan_sales)
To reference a worksheet-level name in another workbook, the sheet name followed by the exclamation point should be included as well, and the workbook name should be enclosed in square brackets. For example:
=SUM([Sales.xlsx]Jan!Jan_sales)
When referencing a named range in a closed workbook, remember to include the full path to your Excel file, for example:
=SUM('C:DocumentsSales.xlsx'!Jan_sales)
How to create an Excel name reference
If you have created a handful of different names in your Excel sheets, you don't need to remember all those names by heart. To insert an Excel name reference in a formula, perform the following steps:
- Select the destination cell, enter the equal sign (=) and start typing your formula or calculation.
- When it comes to the part where you need to insert an Excel name reference, do one of the following:
- If you are referring to a workbook-level name from another workbook, switch to that workbook. If the name resides in another sheet within the same workbook, skip this step.
- If you are making a reference to a worksheet-level name, navigate to that specific sheet either in the current or different workbook.
- Press F3 to open the Past Name dialog window, select the name you want to refer to, and click OK.
- Finish typing your formula or calculation and press the Enter key.
Now that you know how to create an external reference in Excel, you can take a benefit from this great ability and use data from other worksheets and workbooks in your calculations. I thank you for reading and look forward to seeing you on our blog next week!
You may also be interested in
Possible Duplicate:
excel macro: browse excel file and use its worksheet data
excel macro: browse excel file and use its worksheet data
In brief, I want to write a script can do this.In the current excel file, there is a macro button. The user can click the button to browse files (another Excel). In that browsed Excel, several worksheets will be load to current Excel workbook and the data will be used.I write like this
I write the browse function already. but how to write the following part? eg. the certain worksheet's data in objFile will be used in main_function.
Community♦
TJLD22TJLD227022 gold badges44 silver badges1111 bronze badges
marked as duplicate by ЯegDwight, Clyde Lobo, the Tin Man, Filburt, Jason DownSep 12 '12 at 20:44
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
1 Answer
While what you want isn't really clear (I'm not confident I understand your question's intent) I will have a crack at this.
Assuming you have the name of the file you'd like to open in objFile, you can go about extracting data from that spreadsheet like this - I just open the selected workbook, then write the name of all of the sheets in it to Column A in whichever sheet was open before the button was pressed.
Opening a new workbook causes it to be brought to the foreground - so if I didn't grab the active sheet before opening a new book, then set it after opening the new one, you'd end up overwriting data in the workbook you just opened.
enhzflepenhzflep10.8k22 gold badges2222 silver badges4141 bronze badges
Got a question that you can’t ask on public Stack Overflow? Learn more about sharing private information with Stack Overflow for Teams.
Not the answer you're looking for? Browse other questions tagged vbaexcel-vbaexcel or ask your own question.
Summary: The Microsoft Scripting Guys show you how to copy data from one Microsoft Excel spreadsheet to another one by using Windows PowerShell.
Hey, Scripting Guy! I have this monster Microsoft Excel spreadsheet that I need to make changes to. Frankly, it kind of scares me, because we use this spreadsheet for just about everything. It is a great example of shared information. The HR department, the Finance department, the IT department—we all use this spreadsheet. I know I can make a backup copy of the spreadsheet, but the problem is that after I do that, I need to copy the modified data back to the original spreadsheet so that we do not end up with multiple versions. In fact, a couple of years ago, that is exactly what happened. The HR department modified their copy, the Finance department modified their copy, and I was the one who was stuck with reconciling the three spreadsheets. So, what I would like to do is to copy data from one spreadsheet in the workbook and insert the data into columns
— MG
Hello MG,
Microsoft Scripting Guy Ed Wilson here. Well today we are celebrating. It is Hey, Scripting Guy! article number 1,500. The milestones, in terms of numbers, are coming quicker now than they used to do since we started publishing articles seven days a week. During the 2010 Scripting Games, we actually published 186 articles, but because many of those were status updates and the like, we decided to count them as one per day; therefore, I wrote Hey Scripting Guy! article 1,000 for January 1, 2009. That milestone actually snuck up on me, and unfortunately, I did not mention it as article 1,000. Oh, and repeat articles (such as when I was sick) do not count in the total.
MG, back to your question, there was a Hey Scripting Guy! article published five years ago called How Can I Copy Column C of One Worksheet to Column A of a Second Worksheet? Of course, back then the article was about using VBScript to perform the operation. I decided to adapt that script, and while I was at it, I decided to add some additional features to the script. The resultant script is called Set-ExcelUserData.ps1, but you can use this technique for any type of data that is stored in a Microsoft Excel workbook. The complete Set-ExcelUserData.ps1 script is shown here.
Excel Pull Data From Another Workbook
Set-ExcelUserData.ps1
$path= “C:fsoApprovedUsers.xlsx”
$Excel=New-Object-ComObjectexcel.application
$Excel.visible=$false
$Workbook=$excel.Workbooks.open($path)
$Worksheet=$Workbook.WorkSheets.item(“UserNames”)
$worksheet.activate()
$range=$WorkSheet.Range(“A1:B1”).EntireColumn
$range.Copy()|out-null
$Worksheet=$Workbook.Worksheets.item(2)
$Range=$Worksheet.Range(“G1”)
$Worksheet.Paste($range)
$workbook.Save()
$Excel.Quit()
Remove-Variable-Nameexcel
[gc]::collect()
[gc]::WaitForPendingFinalizers()
$Excel=New-Object-ComObjectexcel.application
$Excel.visible=$false
$Workbook=$excel.Workbooks.open($path)
$Worksheet=$Workbook.WorkSheets.item(“UserNames”)
$worksheet.activate()
$range=$WorkSheet.Range(“A1:B1”).EntireColumn
$range.Copy()|out-null
$Worksheet=$Workbook.Worksheets.item(2)
$Range=$Worksheet.Range(“G1”)
$Worksheet.Paste($range)
$workbook.Save()
$Excel.Quit()
Remove-Variable-Nameexcel
[gc]::collect()
[gc]::WaitForPendingFinalizers()
The first thing I do in the Set-ExcelUserData.ps1 script is set the path to the Microsoft Excel workbook. The path to the workbook is stored in the $path variable. Unfortunately, I did not abstract all information into variables. For example, the worksheet name is “UserNames”, the data is stored in range “A1:B1”, and the data is copied to worksheet number 2 and pasted into target range “G1.” A nice improvement to the script would be to add those items to a set of command-line parameters. In this way, the body of the script would be completely abstracted into variables, and it would then be reusable code. As it is, the major design consideration for this script was to illustrate a point, not to provide highly reusable code. The Set-ExcelUserDataParam.ps1 script, shown at the end of this article, illustrates abstracting all hard coded data from the script.
After the path to the Microsoft Excel workbook has been assigned, it is time to create the excel.application object and to make the application invisible. The reason for making the application invisible is to avoid having Microsoft Excel popping up and grabbing focus. Making the application invisible also speeds up the script. This is shown here:
$path= “C:fsoApprovedUsers.xlsx”
$Excel=New-Object-ComObjectexcel.application
$Excel.visible=$false
$Excel=New-Object-ComObjectexcel.application
$Excel.visible=$false
After the application object has been created, it is time to open the workbook and specify the active worksheet. Because the data we wish to copy and paste is on a named spreadsheet, it is necessary to specify the name of the sheet in order to make it the active sheet. The workbook containing the data is shown in the following image.
The name to use is the one that has been specified on the Spreadsheet tab. This portion of the code is shown here:
$Workbook=$excel.Workbooks.open($path)
$Worksheet=$Workbook.WorkSheets.item(“UserNames”)
$worksheet.activate()
$Worksheet=$Workbook.WorkSheets.item(“UserNames”)
$worksheet.activate()
The range object is used to tell Microsoft Excel where to find the source data. In our worksheet, the source data is in column A and column B. When creating range objects, you can specify the starting column/row value, and the ending column/row value, or you can simply tell Microsoft Excel to choose the entire column. This is what I do here because I am not certain of how many user names are contained in the spreadsheet. To tell the range object to use the entire column, use the entirecolumn property from the range object:
Now I copy the selected range object to the clipboard by using the copy method. The copy method returns true if the copy is successful, and because I am not interested in seeing this, I pipe the returned information to the Out-Null cmdlet. The Out-Null cmdlet discards any information that is piped to it:
Excel Lookup Data From Another File
$range.Copy()|out-null
It is time to set up the destination. To do this, I recycle the previous variable names. I specify the worksheet by choosing worksheet 2 from the collection—this value corresponds to the default sheet2. The destination spreadsheet is shown in the following image.
I did this to show you how you can connect to spreadsheets that have either a custom name or a default name. The range is specified as column G, beginning with row 1. It is not necessary to specify range “G1:H1” because by default Microsoft Excel is smart enough to figure out that if you copy two columns of data, you want to paste two columns of data. However, if you do specify the range of “G1:H1”, it will work. This portion of the script is shown here:
$Worksheet=$Workbook.Worksheets.item(2)
$Range=$Worksheet.Range(“G1”)
$Range=$Worksheet.Range(“G1”)
After the target worksheet and range have been created, it is time to paste the previously copied data, save the worksheet, and quit the excel.application object, as shown here:
$Worksheet.Paste($range)
$workbook.Save()
$Excel.Quit()
$workbook.Save()
$Excel.Quit()
To clean up after running the script, delete the $excel variable, and call the collect method from the .NET Framework garbage collection service while waiting for pending finalizers:
Remove-Variable-Nameexcel
[gc]::collect()
[gc]::WaitForPendingFinalizers()
[gc]::collect()
[gc]::WaitForPendingFinalizers()
When the script runs, the workbook contains the copied data that is shown in the following image.
A modified version of the script is Set-ExcelUserDataParam.ps1, which has all hard coded data moved out of the script body and into parameters. The script is shown here.
Set-ExcelUserDataParam.ps1
Param(
$path= “C:fsoApprovedUsers.xlsx”,
$worksheet1= “UserNames”,
$range1= “A1:B1”,
$worksheet2=2,
$range2= “G1”
)#endparam
$Excel=New-Object-ComObjectexcel.application
$Excel.visible=$false
$Workbook=$excel.Workbooks.open($path)
$Worksheet=$Workbook.WorkSheets.item($worksheet1)
$worksheet.activate()
$range=$WorkSheet.Range($range1).EntireColumn
$range.Copy()|out-null
$Worksheet=$Workbook.Worksheets.item($worksheet2)
$Range=$Worksheet.Range($range2)
$Worksheet.Paste($range)
$workbook.Save()
$Excel.Quit()
Remove-Variable-Nameexcel
[gc]::collect()
[gc]::WaitForPendingFinalizers()
$path= “C:fsoApprovedUsers.xlsx”,
$worksheet1= “UserNames”,
$range1= “A1:B1”,
$worksheet2=2,
$range2= “G1”
)#endparam
$Excel=New-Object-ComObjectexcel.application
$Excel.visible=$false
$Workbook=$excel.Workbooks.open($path)
$Worksheet=$Workbook.WorkSheets.item($worksheet1)
$worksheet.activate()
$range=$WorkSheet.Range($range1).EntireColumn
$range.Copy()|out-null
$Worksheet=$Workbook.Worksheets.item($worksheet2)
$Range=$Worksheet.Range($range2)
$Worksheet.Paste($range)
$workbook.Save()
$Excel.Quit()
Remove-Variable-Nameexcel
[gc]::collect()
[gc]::WaitForPendingFinalizers()
MG, that is all there is to using Microsoft Excel to copy data from one spreadsheet to another. Microsoft Excel Week will continue tomorrow when we will talk about importing data from a CSV file into a new Microsoft Excel workbook.
We invite you to follow us on Twitter and Facebook. If you have any questions, send email to us at [email protected], or post your questions on the Official Scripting Guys Forum. See you tomorrow. Until then, peace.
Ed Wilson and Craig Liebendorfer, Scripting Guys
You can create a dynamic connection between an existing Excel workbook and another workbook. You can then refresh the connection whenever the data in the second workbook changes.
Newer versionsOffice 2010 – 2013Office 2007
Use Excel's Get & Transform (Power Query) experience to connect to an Excel workbook.
- Click the Data tab, then Get Data > From File > From Workbook. If you don't see the Get Data button, click New Query > From File > From Workbook.
- Browse to the workbook in the Import Data window.
- In the Navigator window, select the table or worksheet you want to import, then click Load or Edit.
In Excel 2010 and 2013, there are two methods of creating a connection to another workbook. The recommended method is to use Power Query, which is available if you download the Power Query add-in. If you can't download the Power Query add-in, you can use the Data Connection Wizard.
Power Query
- In the Power Query ribbon tab, click From File > From Excel.
- Browse to the workbook.
- In the Navigator window, select the table or worksheet you want to import, then click Load or Edit.
Data Connection Wizard
Step 1: Create a connection with another workbook
- On the Data tab, click Connections.
- In the Workbook Connections dialog box, click Add.
- Near the bottom of the Existing Connections dialog box, click Browse for More.
- Find your workbook, and click Open.
- In the Select Table dialog box, select a table (worksheet), and click OK.Notes:
- Worksheets are referred to as 'tables' in the Select Table dialog box.
- You can only add one table at a time.
- You can rename a table by clicking on the Properties button. You can also add a description.
- To add more tables, repeat steps 2 through 5.
- Click Close.
Step 2: Add the tables to your worksheet
- Click Existing Connections, choose the table, and click Open.
- In the Import Data dialog box, choose where to put the data in your workbook and whether to view the data as a Table, PivotTable, or PivotChart.Note: In Excel 2013, you can add the data to the Data Model so that you can combine your data with other tables or data from other sources, create relationships between tables, and do much more than you can with a basic PivotTable.
Data Connection Wizard
Step 1: Create a connection with another workbook
- On the Data tab, click Connections.
- In the Workbook Connections dialog box, click Add.
- Near the bottom of the Existing Connections dialog box, click Browse for More.
- Find your workbook, and click Open.
- In the Select Table dialog box, select a table (worksheet), and click OK.Notes:
- Worksheets are referred to as 'tables' in the Select Table dialog box
- You can only add one table at a time.
- You can rename a table by clicking on the Properties button. You can also add a description.
- To add more tables, repeat steps 2 through 5.
- Click Close.
Step 2: Add the tables to your worksheet
- Click Existing Connections, choose the table, and click Open.
- In the Import Data dialog box, choose where to put the data in your workbook and whether to view the data as a Table, PivotTable, or PivotChart.
Keep the data in your workbook up to date
Now that you're connected to the external workbook, you'll want its latest data shown in your own workbook. Go to Data > Refresh All to get the latest data. For more information on refreshing, go to Refresh data connected to another workbook.
Need more help?
You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.