INDIRECT and multiple sheets

[Guest]

My workbook is formed by several identical sheets containing a MATCH formula
to select a value from an array residing in another sheet. The sheet name cen
be easily calculated and varies from sheet to sheet.
Therefore the MATCH function execute the same operation on multiple
identical sheets. I'd like tto automate the population of each sheet by
reverting to INDIRECT and a dynamic string as its argument. Can I use it when
referring an array?
Is there another smart way to crreate a formula whose syntax varies
according to the sheet name?

In the example below I'd like to replace the country code UK with another
2-letter combination representing another identical sheet.

=INDIRECT("'"&$A$3&"$p'!Z"&TEXT(MATCH($A5,'UK$p'!$B$7:$B$44,0)+6,"##"))

Thanks, Stefano

 

[T. Valko]

You can do it with another call to INDIRECT

A1 = some country code like UK

=INDIRECT("'"&$A$3&"$p'!Z"&TEXT(MATCH($A5,INDIRECT("'"&A1&"$p'!B7:B44"),0)+6,"##"))

Biff