How to: Create an external reference in different workbooks in Excel

How to: Create an external reference in different workbooks in Excel

Answer ID 2526   |    Published 11/13/2007 03:09 PM   |    Updated 11/13/2007 03:09 PM

How to: Create an external reference in different workbooks in Excel 2007.

Solution
Open the destination and source workbook. To save the destination workbook, click the ‘Microsoft Office’ button and click ‘Save’. Select the cells to create the link. Enter ‘=’. Open source workbook. Select the cells that you want to link to. Press ENTER.

Details
An external reference is a reference to a cell or range on a worksheet in another Excel workbook. You can refer to the contents of cells in another workbook by creating an external reference. You can use the external references while working with large amounts of data or complex formulas.

To create an external reference between cells in different workbooks:

1) Open Microsoft Excel workbook.
2) Open the workbook that contains the link and the workbook that contains the data that you want to link to.
3) To save the destination workbook, click the ‘Microsoft Office’ button on the top left and click ‘Save’.
4) Select the cells in which you want to create the link.
5) Enter the ‘=’ sign.
NOTE: If you want to perform calculations or functions on the link value, type the operator or function that you want to precede the link.
6) Open the source workbook and click the appropriate worksheet.
7) Select the cells that you want to link to.
8) Press ENTER. (This creates a reference between cells of different workbook. You may close the source workbook after creating a link.)
Knowledge-Paks Online and Knowledge-Paks On Site
Copyright (c) 2007 by RightAnswers, Inc. Subject to the terms set forth in the end-user license agreement. All rights reserved.

Advertisements

Access: When exporting the ACCESS File as CSV format it limit the decimal values to 2 points

Scenario:
I had an access file and I have declared the data types of the column to Numeric, Field Size: Double, Format:Percentage, Decimal places:Auto. Even after this, when I was exporting the access query to flat file format (CSV) it was showing only 2 decimal places.

Solution:

  1. Save teh data types of the column to Numeric, Field Size: Double, format: General number, Decimal places: 10, and then Save the access query as excel file format (.xlsx)
  2. Save the data types of the column to Numeric, Field Size: Double, format: Percentage, Decimal places: 10 and
    a4

    1. Go to Control panel–>Region and Language
    2. Click button Additional settings
    3. On the number tab–>No. of digits after decimal: 9

Problem scenario pictures:

a1

s2

Excel: Extract Only Numbers From Text String

Extract Only Numbers From Text String

  1. You will need to insert this formula as an array.
  2. Select a cell in Excel, paste the formula and then press CTRL + SHIFT + ENTER. OR Excel formula surrounds the formula with braces { }.Formula: =MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$101),1)),0),COUNT(1*MID(A1,ROW($1:$101),1)))Formula with braces : {=MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$101),1)),0),COUNT(1*MID(A1,ROW($1:$101),1))) }