Is it possible to dynamically update an Excel Table and have the master report shrink and grow?

[nadia p]

My goal is to update information in a master report whereby adding information to Excel Tables on other worksheets and have the master report automatically shrink or grow the area required as the data between sections can change?

I've looked into several ways to attempt this however in Excel, unlike other applications, as the data within a Table that is filtered dynamically changes Excel doesn't seem to (by default) understand that if 10 rows are added that in needs to move the data below it 10 rows to make room to insert the new data. Other applications handle this much better and don't attempt to overwrite the data below it.

+-------------------- R E P O R T S T A R T --------------------

Credits based on FROM / TO dates. (1/1/2019 to 1/2/2019)
[TABLE="width: 600"]
<tbody>[TR]
[TD]Date
[/TD]
[TD]Transaction
[/TD]
[TD]Credits
[/TD]
[TD]Debits
[/TD]
[/TR]
[TR]
[TD]1/1/2019
[/TD]
[TD]Deposit
[/TD]
[TD]1.00
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/2/2019
[/TD]
[TD]Deposit
[/TD]
[TD]1.00
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



Debits based on FROM / TO dates. (1/1/2019 to 1/2/2019)
[TABLE="width: 600"]
<tbody>[TR]
[TD]Date
[/TD]
[TD]Transaction
[/TD]
[TD]Credits
[/TD]
[TD]Debits
[/TD]
[/TR]
[TR]
[TD]1/1/2019
[/TD]
[TD]Withdrawal
[/TD]
[TD][/TD]
[TD]1.00
[/TD]
[/TR]
[TR]
[TD]1/1/2019
[/TD]
[TD]Withdrawal
[/TD]
[TD][/TD]
[TD]1.00
[/TD]
[/TR]
</tbody>[/TABLE]

+-------------------- R E P O R T E N D --------------------



+-------------------- R E P O R T S T A R T --------------------

Credits based on FROM / TO dates. (1/1/2019 to 1/4/2019)
[TABLE="width: 600"]
<tbody>[TR]
[TD]Date
[/TD]
[TD]Transaction
[/TD]
[TD]Credits
[/TD]
[TD]Debits
[/TD]
[/TR]
[TR]
[TD]1/1/2019
[/TD]
[TD]Deposit
[/TD]
[TD]1.00
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/2/2019
[/TD]
[TD]Refund
[/TD]
[TD]1.00
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/3/2019
[/TD]
[TD]Deposit
[/TD]
[TD]1.00
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/4/2019
[/TD]
[TD]Transfer
[/TD]
[TD]1.00
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

NOTE: The space between the Table (above) and the Table (below) is maintained.

Debits based on FROM / TO dates. (1/1/2019 to 1/4/2019)
[TABLE="width: 600"]
<tbody>[TR]
[TD]Date
[/TD]
[TD]Transaction
[/TD]
[TD]Credits
[/TD]
[TD]Debits
[/TD]
[/TR]
[TR]
[TD]1/1/2019
[/TD]
[TD]Check 1
[/TD]
[TD][/TD]
[TD]1.00
[/TD]
[/TR]
[TR]
[TD]1/2/2019
[/TD]
[TD]Check 2
[/TD]
[TD][/TD]
[TD]1.00
[/TD]
[/TR]
[TR]
[TD]1/3/2019
[/TD]
[TD]Withdrawal
[/TD]
[TD][/TD]
[TD]1.00
[/TD]
[/TR]
[TR]
[TD]1/4/2019
[/TD]
[TD]Debit Card
[/TD]
[TD][/TD]
[TD]1.00
[/TD]
[/TR]
</tbody>[/TABLE]


+-------------------- R E P O R T E N D --------------------

In the example above, per Excels default behavior it would not maintain the distance for 2 additional rows being inserted, it would just overwrite the data below. Or in the case of a PivotTable fail.

There has to be some way other than coding Excel to keep track of everything in a relative database concept while several "topics" (i.e. Credits, Debits, and other transactions) are being updated at the same time.

Under normal circumstances most applications understand when X rows need to be inserted then move X rows down and with certain values change adjust X rows to maintain the distance between objects.

 

[nadia p]

I need to be able to display the information that need to be displayed (i.e. # of rows) per report per topic and print it. If it's 10 pages that is fine.

 

[sandy666]

Try PowerQuery aka Get&Transform

 

[nadia p]

Hello sandy666, firstly thank you for your reply. It appears that Microsoft Power Query for Excel is only available for Windows based computers. I own a Mac using Excel for Mac 2019.

 

[sandy666]

It's not my fault you've a Mac if you wrote in the first post what you can see in my footer would be less misunderstandings.

anyway good luck and have a nice day

 

[nadia p]

My apologies sandy666, I'm new here and had added that information in the tag section. In the future I'll add it into the main body of the post.