4

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?

Thanks!

4 Answers 4

5

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

4
  • 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, 2012 at 0:58
  • I'm sorry, could you be more clear in your question? Mar 24, 2012 at 11:47
  • 1
    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, 2012 at 1:31
  • 1
    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, 2012 at 9:23
3

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.

3

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.

2
  • 1
    This should be a comment to the item in question. Apr 27, 2016 at 20:09
  • but still it is very useful to know of this frustrating lack of proper functionality Feb 1, 2018 at 9:16
0

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.

You must log in to answer this question.

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