How to mirror the column of a table onto a different table automatically

[Jarvo]

Hi everyone,

I'm trying to set up a table (say table 2) that automically shows the contents from a specific column of a different table (say table 1). If a user randomly inserts a new row at any part of table 1, this will need to be done on the other table automatically as well.

Is there a formula or code that allows me to do this please?

Many thanks!

[#2 · Accepted solution]

@Jarvo, try the attached file with this formula into the mirror table:

=INDIRECT("Fruits!" & $C$1 & ROW()-ROW($C$3)+1) & ""

Where $C$1 is a cell to inform a specific column: A; B;...

$C$3 is the cell of the mirror table header.

Regards,

Pedro Wave

[#3]

This has done the trick wonderfully!

Thank you very much for your help

[#4]

@Jarvo 

It is far more straightforward using Microsoft 365.  There, all you need is a to convert the source data to an Excel Table and use a dynamic array formula to read the column you require across.

= Table1[Column2]

Don't be tempted to add fields to the copy because they will not stay aligned. 

[#5]

I understand. Unfortunately the organisation I work for hasn't moved to Microsoft 365 yet, and is unlikely to do so any time soon!

[#6]

This topic refers to: How to mirror the column of a table onto a different table automatically

@Peter Bartholomew, with your solution using Excel for Microsoft 365, the dynamic array formula cannot be inside the mirror table, since dynamic array formulas are not supported in Excel tables. Therefore, one of the topic conditions is not met.

Not to mention that @Jarvo has told us that he doesn't have MS 365.

Regards,
Pedro Wave

[#7]

@PedroWave 

True.  The OP did not specify what they meant by a table, so I chose to assume an Excel 2007 Table with structured references, because that is what I use.  Equally, I use dynamic arrays for calculation with very few exceptions. 

As you suggest, it all depends on the tools you have available.  I, for one, will never use a traditional spreadsheet again.

[#8]

@PedroWave 
I used this formula to mirror a table from one Excel sheet to another, but I cannot copy the table format from sheet 1 to sheet 2. 

For Ex. I have the rows coloured in 2 different colours in the original table, but the mirrored table doesn't have any original formatting. 

Kindly advise. 

[#9]

@Manoj_AI 

If apply formatting by Format Painter?

[#10]

@Sergei Baklan 
It has to be updated automatically whenever I add or delete the rows. For ex. I have a 5 groups with Dark blue and then in each group, I have 5-10 subgroups with Light Blue. So whenever I add or delete the Group or Sub-groups the colour formatting of them has to reflect in the mirrored table too.