INDIRECT formula not calculating properly

[negcx]

I'm using INDIRECT to reference a sheet dynamically. For some reason it's just evaluating to the wrong number. I stepped through the formula's break down with Evaluate Formula feature and it breaks down and parses the formula properly, but then comes up with the wrong value. If I type in manually what INDIRECT comes up with for a formula, it gets the right value..

Excel 2007

Please Login or Register  to view this content.

Any thoughts?

I have sheets with month names and then those sheets have a local named range with a list of sales targets. AK21 is a date.

Thanks,
negcx

[DonkeyOte]

shouldn't that be:

=INDEX(INDIRECT("'"&INDEX(dataMonthNames,MONTH(AK21))&"'!localSalesTarget"),DAY(AK21))

use INDIRECT to create the range to be used in the INDEX, not the formula itself.