#REF! Error

[MathBarnes]

Hello,

I have an Excel spreadsheet that I am using to collect data. I am using formulas and in certain cells the formulas are working but in others they are not- #REF!. I have clicked on the icon to get further information and help resolving the error, but nothing has helped. Please help me with a solution to remove the #REF! errors from the spreadsheet. Thanks in advance!

Some working, but man are not.

[#2]

@MathBarnes 

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

[#3]

@HansVogelaar 

I have uploaded a document. As you complete the cells moving to the right of the spreadsheet, the spreadsheet begins to not automatically use the inserted formula to calculate the numbers.

[#4]

@MathBarnes 

Let's take a look at column H (# of Students Passing 68-79%).

Cells H4 to H11 contain values that look like they have been entered manually.

But the cells from H12 down contain a COUNTIFS formula that refer to a range that cannot be found. Did this workbook originally contain more worksheets that have now been deleted?

[#5]

Hello @HansVogelaar
I deleted the top of the spreadsheet before uploading, but this information was not relevant to the collection of the data. I manually entered some numbers in some of the cells so that you could see that it wasn't calculating. No other worksheets are contained in the original workbook that has been deleted. My colleagues deleted some rows, but I thought that I corrected it when I replaced them and updated the formulas.

[#6]

@MathBarnes 

Take N4. It contains the formula

=COUNTIFS(#REF!,1,#REF!,"a")

The first #REF! must originally have referred to a range elsewhere that contained 1 if a student needs to take the test.

And the second #REF! to a range that contained "a" if the student was absent.

These ranges are not present in the workbook, so I have no way to reconstruct the formula.

[#7]

I think that was it! Thank you @HansVogelaar for your help. Take care :)