Data Extraction

Dear Gurus

Attached is the report I receive from my accounting system. Unfortunately it is not in a format that is useful for analysis.

I would like the report to be in a format in Sheet 1. The accounting system generates report in the format from Sheet 2 to Sheet 15.

I would appreciate any assistance in how to fix this.

Many thanks

Tamil

Thank you for sharing the similar threads. Unfortunately they do not address the problem that I face. In addition I am far removed in using the advanced tools in Excel. Hope someone can assist.

Best regards

Tamil

Hello Tamil,

Here is my solution - it assumes that you will always have Sheet1 set up as per your example.

Enter the following formulas in cells A4:D4, then copy down. You will get the same answers as in your sample with the exception of A17 where the correct answer is 100015 (but you showed 100016 which looks wrong to me)!

A4: =INDIRECT("Sheet"&ROW()-2&"!J31")
B4: =INDIRECT("Sheet"&ROW()-2&"!B3")
C4: =DATEVALUE(MID(INDIRECT("Sheet"&ROW()-2&"!J37"),4,99))
D4: =INDIRECT("Sheet"&ROW()-2&"!R" & MATCH("Payment Amount",INDIRECT("Sheet"&ROW()-2&"!N1:N200"),0))

I hope this helps, please let me know!

Regards,

David


- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

Hi David

Please take a bow! This is exactly what I require. Have a good evening.

Thanks

Tamil

Excellent! Happy to help, and thanks for the Rep.

David