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

nadia p

New Member
Joined
Jul 16, 2019
Messages
8
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)
Date
Transaction
Credits
Debits
1/1/2019
Deposit
1.00
1/2/2019
Deposit
1.00

<tbody>
</tbody>



Debits based on FROM / TO dates. (1/1/2019 to 1/2/2019)
Date
Transaction
Credits
Debits
1/1/2019
Withdrawal
1.00
1/1/2019
Withdrawal
1.00

<tbody>
</tbody>

+-------------------- 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)
Date
Transaction
Credits
Debits
1/1/2019
Deposit
1.00
1/2/2019
Refund
1.00
1/3/2019
Deposit
1.00
1/4/2019
Transfer
1.00

<tbody>
</tbody>

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)
Date
Transaction
Credits
Debits
1/1/2019
Check 1
1.00
1/2/2019
Check 2
1.00
1/3/2019
Withdrawal
1.00
1/4/2019
Debit Card
1.00

<tbody>
</tbody>


+-------------------- 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.
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

nadia p

New Member
Joined
Jul 16, 2019
Messages
8
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.
 

nadia p

New Member
Joined
Jul 16, 2019
Messages
8
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

Well-known Member
Joined
Oct 24, 2015
Messages
7,379
It's not my fault you've a Mac :biggrin: 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

New Member
Joined
Jul 16, 2019
Messages
8
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,278
Messages
5,571,280
Members
412,374
Latest member
Nagelgal
Top