Formula problems 1

[andy7172289]

Hi all,

Is anyone able to decipher the code below? I can get my head around INDIRECT, but the rest is jargon!

[highlight #FCE94F]=IF(ISERROR(INDIRECT("'"&HG$17&"'!M"&MATCH($A5,INDIRECT("'"&HG$17&"'!D"),0))),0,INDIRECT("'"&HG$17&"'!M"&MATCH($A5,INDIRECT("'"&HG$17&"'!D"),0)))[/highlight]

Thanks in Advance

 

[SkipVought]

Hi,

If you have Excel 2007 or greater, this will simplify...
[tt]
=IFERROR(INDIRECT("'"&HG$17&"'!M"&MATCH($A5,INDIRECT("'"&HG$17&"'!D"),0)),0)
[/tt]

So what does it mean:

HG17 must be a Sheet Name
MATCH returns a row offset number that when concatenated with M, results in an A1 style reference to a cell in column M like M1.

Then the INDIRECT converts the text of “SheetName!M1” for instance into a cell reference.

The same thing happens in the MATCH function, where the lookup range is returned from the text of “SheetName!D”

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]