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.

A few questions before I get to my proposed solution.

1. Have you actually created a sheet named '41' in your workbook yet?
2. What is the exact name of the sheet tab that contains the data for item 41? (For example, is it just '41', or is it something like '41 - Kitchen' or 'Sheet41'?)
3. Could there be a typo in the sheet name reference? For instance, is there an extra space in the sheet name (like '41 ' with a space after) or in your formula?
4. Additional information such as operating system (Win ver., mac, etc.), storage medium (OneDrive, Sharepoint, Hard disk, etc.), Excel version, would be helpful in order to get an accurate solution.

My suggestion corresponds to the information you provided in your message.

First:..Rename the sheet OR Copy sheet "40" to create sheet "41"

Fix the formula to match the actual sheet name
If the sheet is named something like "41 - Bathroom", change your formula to:

=IF(D66>0,'41 - Bathroom'!Rate+$G$18,0)

Use INDIRECT for more flexibility 
If you want formulas that automatically adjust, you could use:

=IF(D66>0,INDIRECT("'"&ROW(A41)&"'!Rate")+$G$18,0)

But this is more complex and might not be necessary for your needs.

Try checking if sheet "41" exists first - that's almost certainly the issue!

I hope this helps you with your project.

HelloDave_Self​, 

The #NAME? error occurs when Excel can’t recognize part of a formula. Check that the sheet named 41 exists and that it has a named range called Rate. Named ranges are workbook-scoped, so if Rate was only created on sheet 40, referencing '41'!Rate will cause the error. Also make sure the sheet name has no extra spaces or hidden characters. Once the sheet and named range exist correctly, =IF(D66>0,'41'!Rate+$G$18,0) will work. For more information, see Microsoft support articles titled "How to correct a #NAME? error" and "Use the Name Manager in Excel".