Consolidated Report building using mega formula or VBA

In a workbook there are three sheet (1) Consolidated report (2) report sheet and data which is hidden.

I need a formula or VBA to build a report in sheet named as “consolidated report” which should generate the report as in “report sheet” if I select corresponding validation list (available in “consolidated report” sheet) accordingly. Please let me know if my points are not clear.