Hi all,
I'm facing a performance issue concerning tables and dynamic ranges.
I'm not sure I can do what I want with basic Excel instructions WITHOUT macros...
Let's say that in Sheet1 I have a BIG table (eg. 10000 rows) that periodically grows in size by new inserts.
In Sheet2 I need to have an "adapted" twin table, that references the one in Sheet1. For each column in Sheet2 I use INDIRECT(Table-Sheet1-ColumnX) to keep up with the new insert in Sheet1.
My problem is that in Sheet2 I have to explicitly say in EACH cells of each row of the columns to reference to the corresponding cell of Sheet1, but since Sheet1 rows grow in number, therefore in Sheet2 I have to copy manually the formula INDIRECT(Table-Sheet1-ColumnX) in let's say 100000 rows to cover the potential growth of rows in Sheet1. By doing this Excel calculates hundreds of thousands unnecessary rows with NULL or empty values affecting greatly the performance of the calculations.
My question is: is there a way to tell to Sheet2 to dynamically calculate only the number of rows corresponding to the number of non-empty-rows in the table of Sheet1?
In other words, can I fill cells in Sheet2 automatically from blank cell so that after an insert in Sheet1 they actually display the corresponding values in Sheet2, without having to write the referencing formula explicitly, therefore without having thousands of rows with #VALUE or #REF that Excel has to calculate without unnecessarily?
Thanks in advance for any help.
Example:
BEFORE
Sheet1__________________Sheet2
-------------------________-------------------
Rome | 17 | green |________Rome | 17 | green |
Paris | 20 | red |___________Paris | 20 | red |
London | 9 | blue |_________London | 9 | blue |
? | ? | ? |
? | ? | ? |
*? is new inserts
AFTER NEW INSERTS IN SHEET1
Sheet1____________________Sheet2
-------------------_________-------------------
Rome | 17 | green |_________Rome | 17 | green |
Paris | 20 | red |____________Paris | 20 | red |
London | 9 | blue |__________London | 9 | blue |
Berlin | 34 | purple |_________Berlin | 34 | purple |
? | ? | ? |
*? is new inserts
Without having to write explicitly the formula in Sheet2-Row5 and the following, so Excel doesn't calculate them.
Thank you.
I'm facing a performance issue concerning tables and dynamic ranges.
I'm not sure I can do what I want with basic Excel instructions WITHOUT macros...
Let's say that in Sheet1 I have a BIG table (eg. 10000 rows) that periodically grows in size by new inserts.
In Sheet2 I need to have an "adapted" twin table, that references the one in Sheet1. For each column in Sheet2 I use INDIRECT(Table-Sheet1-ColumnX) to keep up with the new insert in Sheet1.
My problem is that in Sheet2 I have to explicitly say in EACH cells of each row of the columns to reference to the corresponding cell of Sheet1, but since Sheet1 rows grow in number, therefore in Sheet2 I have to copy manually the formula INDIRECT(Table-Sheet1-ColumnX) in let's say 100000 rows to cover the potential growth of rows in Sheet1. By doing this Excel calculates hundreds of thousands unnecessary rows with NULL or empty values affecting greatly the performance of the calculations.
My question is: is there a way to tell to Sheet2 to dynamically calculate only the number of rows corresponding to the number of non-empty-rows in the table of Sheet1?
In other words, can I fill cells in Sheet2 automatically from blank cell so that after an insert in Sheet1 they actually display the corresponding values in Sheet2, without having to write the referencing formula explicitly, therefore without having thousands of rows with #VALUE or #REF that Excel has to calculate without unnecessarily?
Thanks in advance for any help.
Example:
BEFORE
Sheet1__________________Sheet2
-------------------________-------------------
Rome | 17 | green |________Rome | 17 | green |
Paris | 20 | red |___________Paris | 20 | red |
London | 9 | blue |_________London | 9 | blue |
? | ? | ? |
? | ? | ? |
*? is new inserts
AFTER NEW INSERTS IN SHEET1
Sheet1____________________Sheet2
-------------------_________-------------------
Rome | 17 | green |_________Rome | 17 | green |
Paris | 20 | red |____________Paris | 20 | red |
London | 9 | blue |__________London | 9 | blue |
Berlin | 34 | purple |_________Berlin | 34 | purple |
? | ? | ? |
*? is new inserts
Without having to write explicitly the formula in Sheet2-Row5 and the following, so Excel doesn't calculate them.
Thank you.
