Excel, how to insert rows and auto-sum from other worksheets based on two dates?

by nadia p.   Last Updated July 11, 2019 20:01 PM

I would like to be able to lookup information from worksheet-B (History) and automatically insert N number of rows found from worksheet-B into worksheet-A (Tool). The number of lines inserted need to sum the total amount.

The user will enter From and To dates and the Tool will cross reference each corresponding worksheet and insert rows with the information found and autosum the subtotal. Tool: https://i.imgur.com/RRRXhDy.png

Here are examples of the corresponding worksheets. History-Electricity: https://i.imgur.com/BULPm0W.png History-Water: https://i.imgur.com/ry5sg98.png

Here is what the final result should appear as based on the From / To dates entered. Result: https://i.imgur.com/h0Jo9MN.png

Testcase: https://filecentral.se/filecentral/app/en/pub-download.html?c=1a6cb897-0a88-4ba8-8993-1f3c84b1908e&file.id=1191502

I've attempted the following yet been unsuccessful. 1. Pivot table approach 2. INDEX approach 3. VLOOKUP approach

However the end result is that the rows inserted don't automatically sort correctly and the subtotal won't automatically update.

Any advise is greatly appreciated.



Related Questions


Updated August 20, 2019 18:01 PM

Updated September 12, 2019 16:01 PM

Updated July 14, 2019 19:01 PM

Updated September 16, 2019 16:01 PM

Updated February 01, 2019 20:01 PM