Process improvement - Is there a more efficient way? 3 excel sheets

[shakethingsup]

Hi may be confusing, but I'll try my best. The forum and members have helped me out twice before. I am trying to see if the process I'm following is the most efficient.

I am working with 3 different excel workbooks which can be combined into one.

1. workbook 1 - the template and ultimately the final product. The owner of the company prefers the workbook to look in a certain way - see image. This workbook has all the formatting and is the final product but is one month behind in information so we need to update it using workbook 2 and 3.

• Every worksheet is a different property
• Every worksheet looks the same i.e. the information begins on the same row/column
• Arrears are Net O/S > $500.
• Non-rental Arrears are Net O/S < $500.

2. In order to update workbook 1, workbook 2 (only has 1 worksheet) and 3 (only has 1 worksheet) are exported from our accounting software and then the worksheets are moved to workbook 1.

Workbook 2 (shown below) has all information (non-formatted 300-500 rows) in the above table except move in date. Workbook 3 has the move in date. In order to get workbook 2 to a usable state. I perform the following:

• delete blank rows, delete subtotal rows and delete all rows with net o/s amount < $10 using sort/filter.
• I create a column called move in date (circled in red), and vlookup between workbook 2 and 3 to get the date
• I then use a formula to search across all worksheets (shown below) to get comments from our OLD workbook 1 because these are still relevant

=VLOOKUP(A4,INDIRECT("'"&INDEX(SheetList,MATCH(1,--(COUNTIF(INDIRECT("'"&SheetList&"'!$A$1:$c$4"),A4)>0),0))&"'!$A$1:$c$4"),2,FALSE)

Once this is done, I then copy and paste values back into workbook 1 by each property (worksheet).