Auto Update Between Two Tables (Adding Rows/Columns)

ScottTemple · Dec 28, 2023

Hello,

I have two tables on two separate tabs within the same workbook, labelled SHData and SHCOData. When I add new rows, columns and data to SHData I would like that data to populate in the SHCOData table, new rows/columns and data included. Only some of the data from SHData will need to be brought over to SHCOData though.

I've researched this topic online, however, I can only find reference to some VBA codes and either Power Pivot or Power Query, none of which has been able to answer my question.

SanjayGMusafir · Dec 28, 2023

Hi @ScottTemple
Welcome to the Forum

What you want to achieve can be achieved. But important is to understand the table structure and data you want from SHData. Explain that to help you in a better way.

Also remember whatever you are pulling on SHCOData to remain dynamic the data will be Spill Data and not a structured table.

ScottTemple · Jan 2, 2024

SanjayGMusafir said:
Hi @ScottTemple
Welcome to the Forum

What you want to achieve can be achieved. But important is to understand the table structure and data you want from SHData. Explain that to help you in a better way.

Also remember whatever you are pulling on SHCOData to remain dynamic the data will be Spill Data and not a structured table.

Hi @SanjayGMusafir,

I have the following columns in the SHData table (columns A:Q):

Date

Work Order

Grade

Received Dia

Target Dia

Finish Dia.

Scrap Weight

Finish Weight

Shaver #

Start Weight

Operator

COIL#

REPO

Shift

Start Time

Finish Time

Take Up Speed Ft/Min

I would like to bring over the below column data from table SHData to SHCOData (columns A:F):

Date

Received Dia

Shaver #

Shift

Start Time

End Time

What will Spill and into which columns/rows?

SanjayGMusafir · Jan 3, 2024

Sorry for the late response as I was not well.

Check this and revert -

Book1

A

B

C

D

E

F

G

H

I

J

K

1

Column 1

Column 2

Column 3

Column 4

Column 5

Column 6

Column 1

Column 3

Column 4

Column 6

2

Data 1 -1

Data 2 - 1

Data 3 - 1

Data 4 - 1

Data 5 - 1

Data 6 - 1

Data 1 -1

Data 3 - 1

Data 4 - 1

Data 6 - 1

3

Data 1 -2

Data 2 - 2

Data 3 - 2

Data 4 - 2

Data 5 - 2

Data 6 - 2

Data 1 -2

Data 3 - 2

Data 4 - 2

Data 6 - 2

4

Data 1 -3

Data 2 - 3

Data 3 - 3

Data 4 - 3

Data 5 - 3

Data 6 - 3

Data 1 -3

Data 3 - 3

Data 4 - 3

Data 6 - 3

5

Data 1 -4

Data 2 - 4

Data 3 - 4

Data 4 - 4

Data 5 - 4

Data 6 - 4

Data 1 -4

Data 3 - 4

Data 4 - 4

Data 6 - 4

6

Data 1 -5

Data 2 - 5

Data 3 - 5

Data 4 - 5

Data 5 - 5

Data 6 - 5

Data 1 -5

Data 3 - 5

Data 4 - 5

Data 6 - 5

7

Data 1 -6

Data 2 - 6

Data 3 - 6

Data 4 - 6

Data 5 - 6

Data 6 - 6

Data 1 -6

Data 3 - 6

Data 4 - 6

Data 6 - 6

8

Data 1 -7

Data 2 - 7

Data 3 - 7

Data 4 - 7

Data 5 - 7

Data 6 - 7

Data 1 -7

Data 3 - 7

Data 4 - 7

Data 6 - 7

9

Data 1 -8

Data 2 - 8

Data 3 - 8

Data 4 - 8

Data 5 - 8

Data 6 - 8

Data 1 -8

Data 3 - 8

Data 4 - 8

Data 6 - 8

10

Data 1 -9

Data 2 - 9

Data 3 - 9

Data 4 - 9

Data 5 - 9

Data 6 - 9

Data 1 -9

Data 3 - 9

Data 4 - 9

Data 6 - 9

11

Data 1 -10

Data 2 - 10

Data 3 - 10

Data 4 - 10

Data 5 - 10

Data 6 - 10

Data 1 -10

Data 3 - 10

Data 4 - 10

Data 6 - 10

12

Data 1 -11

Data 2 - 11

Data 3 - 11

Data 4 - 11

Data 5 - 11

Data 6 - 11

Data 1 -11

Data 3 - 11

Data 4 - 11

Data 6 - 11

Sheet1

Cell Formulas
Range	Formula
H1:K12	H1	=FILTER(FILTER(A:F,A:A<>""),{1,0,1,1,0,1})
Dynamic array formulas.

I borrowed this idea from the video below -

ScottTemple · Jan 4, 2024

SanjayGMusafir said:
Sorry for the late response as I was not well.

Check this and revert -

Book1
A B C D E F G H I J K
1 Column 1 Column 2 Column 3 Column 4 Column 5 Column 6 Column 1 Column 3 Column 4 Column 6
2 Data 1 -1 Data 2 - 1 Data 3 - 1 Data 4 - 1 Data 5 - 1 Data 6 - 1 Data 1 -1 Data 3 - 1 Data 4 - 1 Data 6 - 1
3 Data 1 -2 Data 2 - 2 Data 3 - 2 Data 4 - 2 Data 5 - 2 Data 6 - 2 Data 1 -2 Data 3 - 2 Data 4 - 2 Data 6 - 2
4 Data 1 -3 Data 2 - 3 Data 3 - 3 Data 4 - 3 Data 5 - 3 Data 6 - 3 Data 1 -3 Data 3 - 3 Data 4 - 3 Data 6 - 3
5 Data 1 -4 Data 2 - 4 Data 3 - 4 Data 4 - 4 Data 5 - 4 Data 6 - 4 Data 1 -4 Data 3 - 4 Data 4 - 4 Data 6 - 4
6 Data 1 -5 Data 2 - 5 Data 3 - 5 Data 4 - 5 Data 5 - 5 Data 6 - 5 Data 1 -5 Data 3 - 5 Data 4 - 5 Data 6 - 5
7 Data 1 -6 Data 2 - 6 Data 3 - 6 Data 4 - 6 Data 5 - 6 Data 6 - 6 Data 1 -6 Data 3 - 6 Data 4 - 6 Data 6 - 6
8 Data 1 -7 Data 2 - 7 Data 3 - 7 Data 4 - 7 Data 5 - 7 Data 6 - 7 Data 1 -7 Data 3 - 7 Data 4 - 7 Data 6 - 7
9 Data 1 -8 Data 2 - 8 Data 3 - 8 Data 4 - 8 Data 5 - 8 Data 6 - 8 Data 1 -8 Data 3 - 8 Data 4 - 8 Data 6 - 8
10 Data 1 -9 Data 2 - 9 Data 3 - 9 Data 4 - 9 Data 5 - 9 Data 6 - 9 Data 1 -9 Data 3 - 9 Data 4 - 9 Data 6 - 9
11 Data 1 -10 Data 2 - 10 Data 3 - 10 Data 4 - 10 Data 5 - 10 Data 6 - 10 Data 1 -10 Data 3 - 10 Data 4 - 10 Data 6 - 10
12 Data 1 -11 Data 2 - 11 Data 3 - 11 Data 4 - 11 Data 5 - 11 Data 6 - 11 Data 1 -11 Data 3 - 11 Data 4 - 11 Data 6 - 11
Sheet1
Cell Formulas
Range Formula
H1:K12 H1 =FILTER(FILTER(A:F,A:A<>""),{1,0,1,1,0,1})
Dynamic array formulas.

I borrowed this idea from the video below -

SanjayGMusafir said:
Sorry for the late response as I was not well.

Check this and revert -

Thanks Sanjay, no worries, I've been under the weather myself the past few weeks, thank you for replying back.

SanjayGMusafir said:
This did work, however, when I attempt to create a table from the data the formula pulled over, I'm receiving a Spill error and the data does not appear (see below):

ScottTemple · Jan 4, 2024

ScottTemple said:
View attachment 104367

What I'm looking to create is a data entry tab and then pull over certain data from that tab into a secondary tab where I will be adding additional columns and formulas. As the first table gets new rows, the second table needs to add these rows so the additional formulas can automatically calculate.

SHData Table Headers:

Date

Work Order

Grade

Received Dia

Target Dia

Finish Dia.

Scrap Weight

Finish Weight

Shaver #

Start Weight

Operator

COIL#

REPO

Shift

Start Time

Finish Time

Take Up Speed Ft/Min

SHCOData Table Headers:

Date

Grade

Received Dia.

Target Dia.

Finish Dia.

Finish Weight

Shaver

Start Time

Finish Time

Density

Table Data Lbs./Ft

Footage

Density

Table Data Lbs./Ft

Footage

Planned Yield Loss (Turn)

Actual Yield % (Turn)

Draw Yield Loss (%)

Draw Yield Loss (lbs)

Yield % (Weight)

The red highlighted text is the data that I want to pull from the SHData table and the black text will contain formulas that will pull from the red text data columns. The reason I'm trying to connect tables is to avoid having to drag and autofill cells as new rows are added with data.

SanjayGMusafir · Jan 4, 2024

The #SPILL error will come if you convert the Spill range to a table

Keep it as a normal and it shall work like charm.

Try and revert

Just to add - Just in case you want it to be sorted in some order that we can handle later.

First test and get the desired results

ScottTemple · Jan 4, 2024

SanjayGMusafir said:
The #SPILL error will come if you convert the Spill range to a table

Keep it as a normal and it shall work like charm.

Try and revert

Just to add - Just in case you want it to be sorted in some order that we can handle later.

First test and get the desired results

I'm not sure if you noticed, but I added an addendum to my follow up reply, still hoping to determine if we can link those two tables?

SanjayGMusafir · Jan 4, 2024

ScottTemple said:
I'm not sure if you noticed, but I added an addendum to my follow up reply, still hoping to determine if we can link those two tables?

I actually overlooked that

We can do that without converting Data to Formatted tables. And if the data (formulae) you are adding in new columns is linked to the pulled data that can be handled so that as rows grow they also get copied down automatically. But it shall depend on kind of formule. Most get pulled down automatically being linked to a spill data (array).

Hope it helps you in some way

You start and then we can sort with the formulae in added columns - Either on this thread or new thread as the Forum Rules shall permit.

ScottTemple · Jan 4, 2024

SanjayGMusafir said:
I actually overlooked that

We can do that without converting Data to Formatted tables. And if the data (formulae) you are adding in new columns is linked to the pulled data that can be handled so that as rows grow they also get copied down automatically. But it shall depend on kind of formule. Most get pulled down automatically being linked to a spill data (array).

Hope it helps you in some way

You start and then we can sort with the formulae in added columns - Either on this thread or new thread as the Forum Rules shall permit.

Auto Update Between Two Tables (Adding Rows/Columns)

ScottTemple

Board Regular

Excel Facts

SanjayGMusafir

Well-known Member

ScottTemple

Board Regular

SanjayGMusafir

Well-known Member

ScottTemple

Board Regular

ScottTemple

Board Regular

SanjayGMusafir

Well-known Member

ScottTemple

Board Regular

SanjayGMusafir

Well-known Member

ScottTemple

Board Regular

Similar threads

Forum statistics

Share this page

Auto Update Between Two Tables (Adding Rows/Columns)

Board Regular

Excel Facts

Well-known Member

Board Regular

Well-known Member

Board Regular

Board Regular

Well-known Member

Board Regular

Well-known Member

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock