CODEWITHSUNDEEP
exceldynamicreferenceworksheet
TheBigAmbiguous
TheBigAmbiguous

Reputation: 303

Linking a whole worksheet to another in Excel

I have two workbooks that have sheets that logically should look identical. I don't want to manually maintain their identical-ness, so I want to dynamically link them (just as you'd do between cells using formulas: "=A1"). I want to link the whole spreadsheet, not just a few cells.

Is there a way to do this without making 238427398729 formulas, thus crashing my machine? Is there a way to say "=sheet2" as a formula defining the content of a whole sheet?

Upvotes: 5

Views: 57096

Answers (4)

stultitia
stultitia

Reputation: 1

You could use arrays for this.

Select the first cell (A1) in the worksheet that you want the data to be linked and put in the following formula for the first 100x100 cells to be linked:

='file_path[file_name]sheet_name'!A1:CV100

Upvotes: 0

Emily
Emily

Reputation: 11

Open your source workbook - copy the range that you want to import (add further rows if your source data is dynamic and the amount of data will change each day/month etc).

In your final workbook (where you will use your version of the data) - select cell A1 and right click

Choose "Paste Link"

Your cells should immediately populate with individual formulas for all cells.

Upvotes: 1

Richard
Richard

Reputation: 31

To dynamically link an entire sheet - manually:

Create a new sheet, in this case Sheet5 will duplicate Sheet3

in the A1 cell of the new sheet reference the source sheet =Sheet3!A1

Then select cell A1 in the target sheet (Sheet5 in my case) and use the lower right handle in the cell to drag it over to width of cells you need. In my case it was over to the "I" column.

Next select the cells in the 1st row of the target sheet that you want to use, and again use the handle in the lower right to drag down to as many rows as you need, in my case down to row 60.

Then go back to the source sheet and select the entire sheet (click the square between the row numbers and the column letters) and then click the Format Painter and switch to the target sheet and apply it to the whole sheet (click the square between the row numbers and the column letters).

You may find 0 in some cells, so you will have to manually clear content in those cells. You may also have to change the width of cell columns to match the original. Still, it is a pretty good duplicate that remains dynamically linked.

In my case I wanted the same exact data but wanted to have some columns invisible for print purposes. So for me, I just changed the font for the columns I did not want to have visible when printing to white. Thus I have a worksheet for preparing data, and a worksheet for printing.

Upvotes: 3

chris neilsen
chris neilsen

Reputation: 53126

You may be able to use the "Get External Data" feature (depending on the layout of your source sheet)

Steps to create in Excel 2010 (other versions menus are a little diferent)

  1. Data tab
  2. Get External data / From other Sources
  3. From Microsoft Query
  4. New data Source
  5. Enter a name
  6. Microsoft Excel Driver
  7. Connect and select source workbook
  8. Select required Sheet
  9. OK, to open Query
  10. Drag the * onto the grid
  11. File / Return data to Excel
  12. Select Destination

Resulting linked query can be manually and / or periodically refreshed

Upvotes: 5

Related Questions