+ Reply to Thread
Results 1 to 4 of 4

Data connection range

  1. #1
    Registered User
    Join Date
    02-14-2009
    Location
    Bristol, England
    MS-Off Ver
    Excel 2010
    Posts
    9

    Data connection range

    Hi, I was wondering whether it's possible to connect one file to just a range of cells in another.

    Essentially I have 2 files: A.xlsx and file B.xlsx.

    A.xslx contains 1 sheet with some calculations and 3 tables. One of the three tables is a costs table that I need to put into B.xlsx, so that B.xlsx will update if I change or add additional data to my costs table in A.xlsx.

    However, in B.xlsx, I would like to have the costs data from A.xlsx as part of another table like so: (I have ensured the column number and headers match)

    ---------------------
    Table headers
    ---------------------
    B.xlsx data
    ...
    ...
    A.xlsx costs table
    ...
    ...
    B.xlsx data
    ...
    ...
    ---------------------
    Totals row
    ---------------------

    (Because these data are part of a report, I must present them in one big table.)

    My problem is that when I import the data from A.xlsx, I only have the option to import the whole file when I only need my costs table. Every time I delete the extra imported data it comes back again when I refresh the remaining data, since the connection in B.xslx is with the whole of A.xlsx.

    Can I just connect a range of A.xlsx with B.xlsx? Or even better just a particular table in A.xlsx?

    Thanks very much
    Last edited by tunaaa; 02-14-2009 at 06:23 PM.

  2. #2
    Forum Contributor
    Join Date
    04-21-2007
    Location
    Lima, Peru
    MS-Off Ver
    2000, 2007, 2010
    Posts
    674

    Re: Data connection range

    Hi

    You can very easily link ranges in two workbooks, from the source, select the range and copy using ctrl-c, select the destination cell in the second workbook and use paste special/paste link. Cells will automatically update when both workbooks are open, if the source is closed when you open the destination you will be prompted to update linked cells when you open the workbook.

    If you need more detail please try this link
    HTML Code: 
    http://www.teachexcel.com/tutorials/importexport/cell_linking_multiple_workbooks.php
    Regards


    Jeff

  3. #3
    Registered User
    Join Date
    02-14-2009
    Location
    Bristol, England
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Data connection range

    Thanks Jeff - that way does everything I want except for one thing. For the report I often need to add new values and new rows up and down the table, and these changes won't be reflected just by pasting as a link. So every time I add new rows in file A, file B will still need changing - is there any way so that I won't have to change file B at all?

    Thanks

  4. #4
    Forum Contributor
    Join Date
    04-21-2007
    Location
    Lima, Peru
    MS-Off Ver
    2000, 2007, 2010
    Posts
    674

    Re: Data connection range

    Hi

    It is possible to use a macro to insert appropriate lines / or shift cells down as appropriate and then insert the necessary link. Will the data be matching in both sheets, i.e. will both tables be identical ?

    If you have an example sheet from each workbook, without data, it would be easier to prepare something for you.

    Regards

    Jeff
    Last edited by solnajeff; 02-14-2009 at 08:58 PM. Reason: Typo

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1