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 July 14, 2019 19:01 PM

Updated February 01, 2019 20:01 PM

Updated June 11, 2019 21:01 PM

Updated June 13, 2019 11:01 AM

Updated March 29, 2019 12:01 PM