Inconsistent column formula

[hamohd70]

Can you please help me with this: why am I getting "inconsistent column formula" when adding new row to the table??

to try it:

stand on the last cell of the last row ..
Press TAB to move to the row which will be automatically added..
notice the green error icons start to appear on some cell .. like P458, Q458, T457, T458...etc..

the file is here.


Thanks for the help

 

[mole999]

you can dismiss the error if you are happy with the output. from the typed list you have P458, Q458, T457, T458, and its warning you that 57 isn't a natural progression of the 58s

 

[joeu2004]

Can you please help me with this: why am I getting "inconsistent column formula" when adding new row to the table??

As long as the formulas are doing what you want, you can and should ignore the "error" icons that Excel displays in the corner of cells.

They are warnings, not bona fide errors. Excel is simply directing your attention to the fact that something seems different about those cells. But the operative words are "seems" and "different". Excel is very bad at understanding human intent, IMHO.

If the warnings distract or bother you, simply disable them. I do. Click on Excel Options > Formulas and uncheckmark the option "Enable background error checking". Also click "Reset Ignored Errors" to remove existing warnings.

 

[hamohd70]

Thanks for the help..but..

The error can not be ignored! The auto filled formulas contain an error which will eventually affect the final result.
Besides, columns starting from N onward are suppose to be hidden from user so he will not have to use to fill handle every time he adds a new row to automatically correct the formulas.

 

[mole999]

so its doing a good thing, and you need to fix the references. Most on site won't visit (or can't) cloud based documents

 

[hamohd70]

unfortunately, I could not locate the attachment function in the forum..

 

[mole999]

unfortunately, I could not locate the attachment function in the forum..

http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html

 

[Jon Peltier]

As long as the formulas are doing what you want

I see this response everywhere someone asks this question.

The fact is, if you're seeing this error, Excel thinks something is inconsistent with your formulas, even if they are apparently the same. Where it will hurt you is when you add a row, and instead of inserting the formula that looks okay, Excel may some day decide to insert the formula it thinks is the actual column formula. And then it will really be inconsistent.

What you need to do is clear the column of its formulas, then reenter the formula in the top row, and let Excel autofill the column. Then your column formulas will finally be consistent.