@cadco1968
IMHO, the "connection" (reference) to an "external"(?) worksheet (i.e, another worksheet in the same workbook?) is irrelevant.
All that matters is the values (not the formulas) in the cells in column B.
When the formula is copied down, the references to B8 change to B9, B10, B11 etc.
So the question that needs to be asked is: what are the values in B9, B10, B11 etc?
-----
Presumably, they are not any of the numeric values zero through 6.
If that is expected, the problem is simple: your formula does not cover that case.
And you have not told us what you want the result to be in that case.
Certainly, your formula can be (and should be) improved. But at a minimum, perhaps one the following does what you want:
=IF(B8=4,0,IF(B8=3,-10,IF(B8=2,-25,IF(B8=1,-50,IF(B8=5,-15,
IF(B8=6,-25,"n/a"))))))
or
=IF(B8=4,0,IF(B8=3,-10,IF(B8=2,-25,IF(B8=1,-50,IF(B8=5,-15,
IF(B8=6,-25,IF(B8=0,"n/a","")))))))
The first formula displays "n/a" when the value in column B is none of 1 through 6, including zero.
The second formula displays the null string (the cell appears blank) when the value in B is none of zero through 6. Replace the null string ("") with anything else you would like.
-----
If you expect that all of the values in column B are zero through 6, you need to investigate why that is not the case.
First, confirm that they are indeed numeric.
Use a formula like =ISNUMBER(B9). Looks can be deceiving. And the format of the cell does not matter. We can have text that looks like a number in a cell that is formatted as General, Number, Currency etc. It is still text.
Second, if the data are indeed numeric, they might appear to be zero through 6 due to formatting limitations, but in fact they are not exactly.
Temporarily format the cells to display 15 decimal places. That might reveal unintended decimal fractions.
But sometimes, there are infinitesimal differences (decimal fractions beyond 15 decimal places) that we cannot see because Excel formats only up to 15 significant digits.
One way to discover the infinitesimal differences is with a formula like
=SUM(B9, -ROUND(B9,15)), formatted as General or Scientific (preferred).
(FYI, we cannot rely on a simpler formula like =B9-ROUND(B9,15) because of a flawed behavior of Excel that is designed to hide infinitesimal differences. Klunk!)
The infinitesimal differences might be of the form 1.23E-16. That means 1.23 times 10 to the -16 power (1/10^16).
-----
If you need further help in troubleshooting the problem, please attach an example Excel file that demonstrates the problem. Click the "browse" link below.
If the forum does not allow you to attach a file, upload the Excel file to a file-sharing website, and post the download URL. I like box.net/files; others like dropbox.com.
