Changed named range not updating in formulas

Hey, all. First, let me apologize for not uploading a copy of the spreadsheet. It has some company info that can't be public. That being said, here's the scenario:
- Excel 2010
- Workbook has a named range, we'll call it named_range on sheet1 referencing cells A1:B100.
- Sheet2 has a slew of formulas that reference named_range.
- All is happy and joyful, everything calculates as expected.
- User updates the named range to instead include sheet1, cells C1:D100.
- All formulas on sheet2 now give the #Value! error.
- Formula example: =SUMPRODUCT(named_range, 'Sheet3'!$A$1:$B$100)/SUM(named_range

What I've tried:
- CTRL+ALT+SHIFT+F9, SHIFT+F9, and F9 to recalculate all formulas. No dice.
- Find and replace "=" with "=" also does nothing.
- Calculation Options are set to automatic.
- If we delete the formula and retype exactly the same (case and all), the formula works.
- Copying the formula out of the formula bar, deleting the cell contents, and pasting the formula back in still yields the #Value! error.

So I thought maybe it was something with the named range and tried recreating it...nope, same results. Also, any named range does this, not just named_range.

I'm lost . Thank you for any help, in advance.

You need to look at the Scope of the second named_range - it might be defined as having worksheet scope rather than workbook scope, and so it will not be accessible in a different sheet.

Hope this helps.

Pete

Scope was correct as well as the defined range. As it turns out, the user had defined the 'Sheet3'!$A$1:$B$100 section incorrectly and had included some invalid data in there. Thanks for the assistance, all.