Linking table data from 1 sheet into another

[RachelMads02]

Hi guys,

Attached is a sample file. On the sheet titled "Conference Stats" are long countif and countifs formulas in columns titled "Tourney App.", "Amount" and "Wins". I chose to remove them from the example because they make the workbook too large to upload, but you can reference them from post 16 of https://www.excelforum.com/excel-for...e-pages-2.html. Much thanks to Cantosh for the help!! It took some time.

There are a lot of these formulas in the "Conference Stats" page and it's creating a lag when calculating and loading the workbook. I'd like to find a way to lessen the calculating and loading times. Since these countifs formulas are counting data contained in the tables (Range D:N) on pages 2-8 (titled #1 vs #16, #2 vs #15 up to #8 vs #9) I thought that if i could copy all the tables to another sheet ("Linked") i could edit the countifs formulas from scanning 8 pages to only scanning 1. So here are my questions:

1. Can this be done, and if so, how? Remember that the tables in the pages 2-8 will have new data added each year so if the tables are copied to another sheet per say, they would have to account for future updates.

2. Will doing this lessen the calculation and loading times?

3. Are calculation and loading times due to the sheer length of formulas or the complexity of the formula? More specifically does my workbook lag because the countifs are long, or that the countifs have to scan 8 different pages?

Thank you in advance!

[avk]

plz show desired result in "Linked" sheet.

[RachelMads02]

It's probably going to make the workbook too large to upload. The theory is quite simple. Look at the pages:

#1 vs #16
#2 vs #15
#3 vs #14
#4 vs #13
#5 vs #12
#6 vs #11
#7 vs #10
#8 vs #9

On each of those pages there is a table from D3:N135. So since there are 8 pages there will be 8 tables (1 from each page). I want to copy them to the "Linked" page. The first table from page "#1 vs #16" will go from A3:L135 on the "Linked" page, then skip a column and the table from the #2 vs #15 page will go from N3:X135, skip a column. Rinse wash repeat until all the tables are successfully copied.

I'm not looking for a simple copy and past option, but rather when i enter data on page "#1 vs #16" it will automatically update the respected table on the "Linked" page.

It's the same theory as taking a cell such as A2 and entering the formula =B2 where whatever data is inserted into cell B2 will it also appear in A2.

[avk]

I am tried our level best. Hope what ever you required its a fulfilled.
Refer Attach file.
As per your above Post #3 i will do necessary things.
Firstly I rename all sheet name. Its due to formula. Because # (special characters) formula not work. Its a regular pratics in Excel.
All sheet "Game" column : I use unique number list in "Linked" sheet. All other column use simple index match formula.
If you add new data in sheet 1_vs_16 auto update in linked sheet. If not added then simply copy paste in respective cell (In liked sheet)

[RachelMads02]

didn't work. when inserting data in the 2nd sheet title 1_vs_16 it did not automatically update in the "linked" page. I want to avoid copy and pasting the data. That's the whole point of having a formula that would do it automatically.

[avk]

Re- attach kindly check it. I have check out.

If not added then simply copy paste in respective cell (In liked sheet) 

[RachelMads02]

So it doesn't automatically update on the linked page? I have to manually go to the linked page and extend each row to show the changes done on the pages 2-9?

[JohnTopley]

Changes in sheets ARE reflected in LINKED page.: try changing a field.

OR try

in A3


=INDEX(Table3[#All],ROWS($1:1),COLUMNS($A:A))

Copy across to K and down

Change "Table", put formula in M3 and repeat for other tabs

[RachelMads02]

the formula works, but i still have to go into the linked page and drag the last row down 1 row to show the changes when they are made in the other sheets. It's not displaying automatically. why is this?

[JohnTopley]

I don't understand I change data ANYWHERE in a "Source" sheet and it is reflected in the "Linked".

Obviously if you add rows to a source sheet then you will need to ensure the rows in "Linked" have been dragged down far enough.

In this case use

=IFERROR(=INDEX(Table3[#All],ROWS($1:1),COLUMNS($A:A)),"")

[RachelMads02]

That was my point. If i have to drag down the rows in the "Linked" page than it defeats the purpose of having a formula. I could simply copy the new line from the "Source" sheet and paste it on the "Linked".

If i was to enter 136 in cell D139 of the "Source" page, is there a way to automatically see the number 136 appear in cell A139 of the "Linked" page without having to drag, copy, add, or adjust anything on the "Linked" page?

[JohnTopley]

Just copy the formula down ONCE and you will never need to do it again: so copy down 300 rows and that is it.

Use this and drag down for your maximum number of rows.

in A3

=IFERROR(INDEX(Table1[#All],ROWS($1:1),COLUMNS($A:A)),"")

drag down 300 rows

NOW add an entry into Table 1 and see "Linked"

[JohnTopley]

See attached

[RachelMads02]

Okay, how can i tweak that formula to not show any zeros when data isn't entered in the source page?

[JohnTopley]

Custom Format cells as 0;;;@

[RachelMads02]

Thank you for your help John!

[JohnTopley]

You're very welcome.