Formula Help

As already mentioned it appears Excel doesn't recognize something in the formula that appears to be a name.  Since the difference in the formulas is the:    '41'!Rate
that is probably the problem.  I suspect sheet '41' exists and it is truely '41' and not '41 ' or ' 41' or the like.  next I would check on Rate and the best way is to use the Name Manager:

So the top arrow shows you select 'Formula' ribbon and then select 'Name Manager' and the popup window will show.  Each defined 'name' will show in the window (unless some filter(s) are on).  The arrow on the right side point to the 'SCOPE' of that name so in this case Rate2 is defined on Sheet8 with scope of Sheet8 and on Sheet4 with scope of workbook.  So if on sheet1 you say =Rate2 you will get the name from Sheet 4 but if you say =Sheet8!Rate2 you will always get the Rate2 from Sheet8. So the good news is that it appear scope referencing isn't an issue here because if 'RATE' was defined on any sheet as 'workbook' scope then it would have sort of worked but given you the wrong data.

The important point here is that you need to make sure you have 'NAMES' for each sheet that needs that name range.  If it doesn't exist you will need to create it using 'New', type the name ('Rate') and select the Scope dropdown to be that sheet ('41') and in the formula bar at the bottom make sure if refers specifically to that sheet name and range.