Macro to Find specific cell and expand into an array using offset func

[jackcel]

Dear all,

I am looking to create a summary sheet ("summary") which pulls data from respective tabs labeled in Year and Quarter formats ("2014Q3"). Probably easier for me to send the excel over.

Due to the non-standardized nature of the data source and multiple tables within a tab ("2014Q3"), I can't really apply an indirect function to extract data.

I would like to create a macro to:

1. search tabs by referencing the headers in the summary sheet (see below)
2. search the postal districts and parameters listed in the summary sheet (ie postal district 03, < 100 sqm etc - thought of applying an index match for this.)
3. populate the data in the summary sheet.

I have no clue on how to accomplish this besides forming each array manually using an index match function.

Thank you so much!

Jack

Sample Table 1 in 2014Q3 - there are multiple tables of different postal districts and rental for each level and size. I thought of standardizing the top label into say Level 1 25th - so i can apply an indirect function. But how can i automate this through a macro? Each quarter has multiple tables.

Postal District	Level 1			Level 2 or higher
05	25th	Median	75th	25th	Median	75th
	Percentile		Percentile	Percentile		Percentile
< 100 sqm	*	*	*	*	*	*
100 - < 200 sqm	20.69	20.93	21.63	17.12	20.14	20.76
200 - < 300 sqm	*	*	*	18.44	19.10	22.18
300 - < 1,000 sqm	*	*	*	28.48	28.98	41.44
>= 1,000 sqm	*	*	*	*	*	*

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

Summary Sheet

Postal District

03

These are the parameters i intend to put into the dynamic formula

Size

< 100 sqm

Level

Level 1

Type

25th

2012Q1

2012Q2

2012Q3

2012Q4

2013Q1

2013Q2

2013Q3

2013Q4

2014Q1

2014Q2

2014Q3

2014Q4

Rent (PSM)

<colgroup><col><col span="12"></colgroup><tbody>
</tbody>