Forum Discussion
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?....
A few questions before I get to my proposed solution.
- Have you actually created a sheet named '41' in your workbook yet?
- 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'?)
- 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?
- 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.