INDIRECT and multiple sheets
 

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

INDIRECT and multiple sheets
 

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

"smaruzzi" wrote in message
...
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