Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am using Excel 2010.

I got a sheet name "March" March Sheet

I would like to duplicate it[This is a table] (with reference / link) to another sheet called "Summary" which included January, Feb, and March.

In the summary tab I would add another column called "Month" and will duplicate the value "March" or their tab month.

I did some research on it, they said copy and paste special with link, but I can't find it.

I need to reference the whole table, just in case any change add / remove the row of the table, the summary still will update it. Is there any solution with this?


share|improve this question

You copy and then right click paste special and link is at the bottom, it's a button in 2010

enter image description here

Alternatively, it should be in your paste menu when you right click: enter image description here

It's the one that looks like a chain (link) or an infinity symbol

share|improve this answer
How about if the original table, added record, I think this link does not work what I ask about the feature, and add another column reference the month. – Shiro Mar 23 '12 at 0:58
I'm sorry, could you be more clear in your question? – Raystafarian Mar 24 '12 at 11:47
I would like to archieve 2 objectives. First, I would like to duplicate a table from a sheet - S(SOURCE) to another sheet - D (Destination). When S did a update(add/delete), D also will update the data/record/row. Lets say Sheet S is from the month of March. Once I duplicated it to Sheet D. I would like to add another column named "Month" and have records of March. The Sheet D will be the summary for many month. Hope I have my question clear. Thanks! – Shiro Mar 26 '12 at 1:31
Hi, I only work on normal range, but my object is table, so this solution is not applicable to me. Any more solution? – Shiro Apr 2 '12 at 9:23
up vote 2 down vote accepted

Microsoft Excel is not able to reference a Table with reference / link to another sheet. It only worked with Range.

With range you can reference like this. Sheet1!$A$1

But it is not possible with Table.

share|improve this answer

The answer from @Raystafarian does not work when selecting the whole table. For some reason Excel doesn't allow the paste with links. However it can be done by copying it twice, first time with only headers and second time with only data. I don't get that.

share|improve this answer
This should be a comment to the item in question. – kevininspace Apr 27 at 20:09

There is a workaround to this... albeit an ugly one.

You can achieve the desired effect by first importing the Table from the Excel Source File into an Access DB (using linked tables for example), then importing that Access Table into your final Excel Destination file.

However, if you want things to update automatically, it requires that you write come small code to open the Access DB and update the files there, before the data is correct in your destination file.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .