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.
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top