Forum Discussion

Dave_Self's avatar
Dave_Self
Occasional Reader
Feb 20, 2026

Formula Help

I have a spreadsheet I use for bidding construction work. On items 1-40 the formula in cells G22-G65 work like they are supposed to. From G66 to G88 where the sheet stops, the formula returns #NAME?....
excel
Formulas and Functions
NikolinoDE's avatar
NikolinoDE
Platinum Contributor
Feb 23, 2026

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.