Formula Help

Based on your latest update, you've confirmed that the sheets (41 through 46) do exist, which eliminates the most common culprit. The fact that data flows to those sheets but doesn't come back is a critical clue. This strongly points away from a simple typo and toward a scope issue with the named range Rate.

What probably happened…

1. You set up Sheet '40' and defined "Rate" (e.g., in cell '40'!$B$5) using the Name Manager.
2. When you created sheets '41' through '46' (likely by copying Sheet '40' or creating them manually), the Name Manager did not automatically create a "Rate" name for those specific sheets.
3. Even though the sheet is named '41', Excel looks for a Named Range called "Rate" scoped specifically to that sheet, doesn't find it, and throws the #NAME? error. It does not automatically borrow the "Rate" from sheet '40'.

Check….

The "Name Manager" Audit

This is the cleanest way to fix the workbook so you don't have to rewrite formulas.

Go to the Formulas tab on the ribbon and click Name Manager.

In the list, look for the name Rate.

Look at the Refers To column. Does it say =Sheet40!$B$5 (or similar)?

The Fix...

If you have many sheets, change the Scope of "Rate" to Workbook.

OR, if the "Rate" cell is in a different location on every sheet (e.g., always cell B5), you might be better off deleting the Named Range entirely and using direct cell references (see Solution 3).

OR, if you must keep the Named Range, you need to ensure every single sheet (40, 41, 42...) has its own "Rate" name defined in the manager. Note: If you copied the sheets, the new "Rate" names might be pointing back to Sheet 40's cells. You must edit them to point to their own sheet.

Or use…

The "Brute Force" Reference

Named Ranges are great for readability, but terrible for copying sheets if not managed perfectly. The most "bulletproof" method in construction estimating is to stop using Named Ranges for single cells and just point to the cell directly.

1. Go to sheet '41'.
2. Find the cell that contains the rate (let's assume it is cell C5).
3. Change the formula in your WBS sheet (G66) to:
   =IF(D66>0, '41'!$C$5 + $G$18, 0)

If you have many sheets, this is tedious, but it eliminates the #NAME? error because Excel never has to "guess" what "Rate" means; you are giving it the exact GPS coordinates.

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".