hey guys,

i've come up with this formula that summarizes data from different worksheets within the same workbook.

=INDIRECT(ADDRESS(MATCH($B3,'Sheet Name'!$A$1:$A$200,0),MATCH(F$2,'Sheet Name'!$A$2:$CA$2,0),1,,$E3))

it works fine, but i would like to automate which sheet name the two match formulas reference. i've been doing so manually so far.

the formula is intended to return the value of say banana sales in a particular year. however i need to build a summary table of banana sales in different countries for a period of 30 years. the individual worksheets are structured so that each sheet has a row lablled banana sales in column A, but not necessarily in the same row and similarly for the year, it will always be in row 2 ,but not necessarily in the same column.

any help or alternative suggestions on how to approach the problem would be greatly appreciated.