A company has one of their invoice reports, where invoices are maintained sheet wise in a single workbook. Structure of the invoice is same across the sheets. Refer the screenshot given below:
Steve who is working as an executive in finance department, wants to create a summary of all the invoices in a single table as line items in a separate sheet. Refer the screenshot below for the required output format:
Once the master sheet is ready, Steve would further take this summary table as a source for a pivot table for his further data analysis.
We need to help Steve by building a formula which will help him to pull the required details from all the invoice sheets irrespective of numbers. Formula which we are building to extract details from the first invoice sheet, we should be able to copy and paste the same formula for the remaining invoices, same should work even when they add new invoice sheets in future.
Download the excel file by clicking here
You can post your solution in the comments section