Data connection range

**tunaaa** · 02-14-2009, 06:15 PM

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

**solnajeff** · 02-14-2009, 07:16 PM

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:

Regards

Jeff

**tunaaa** · 02-14-2009, 08:14 PM

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

**solnajeff** · 02-14-2009, 08:57 PM

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