Tuesday, June 14, 2011

How to Use Indirect in Excel 2007 to Link to Another Worksheet


1. Create a tab for links to external sources by right clicking on the tab named Sheet1 and selecting 'Create New Tab.' Name it 'Links.'
2. Enter an '=' in the formula bar in the cell you want the data imported into, and then switch to the spreadsheet you're pulling data from, and click on the cell the data is in. This will create a formula in the form of ='[Accounting.xls]Sheet1'!$A$1 where '[Accounting.xls]' is replaced with the name of the spreadsheet, 'Sheet1' is replaced with the name of the tab in the work sheet you're pulling data from, and '$A$1' is replaced with the reference to the cell the data resides in.
3. Select the cell you just made the reference in. Click on the Formulas tab in Excel 2007 and choose Name Manager. In earlier versions of Excel, go to the Formulas menu and select 'Names.' Assign a name to Links!A1 that will be easy to remember. For this example, choose 'Accounting.'
4. Enter the following formula to reference the data: '=INDIRECT(Accounting)'. This will display the information in the cell with the name Accounting.

Blogger news