MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Automatic item (line) counter

Posted by Joan L. on September 15, 2001 5:28 PM

I am looking for a way of automating a column I call "Item Counter," on an Excel log that I keep for adjustments. It’s not critical, but I usually am in a hurry and sometimes forget as I number hard copies that are filed in a cabinet alphabetically, not chronologically. It’s hard to imagine but things get mixed up with over 2,000 lines of adjustment. Anyhow, what I am looking for specifically, is the following. In cell A5 (for example), I want it to be BLANK, until I put ANY data in cell A6. That is, the cell will show NOTHING until I put data into A6, then A5 should equal A4+1, with the first item in the log carrying the starting #.

(Got any ideas? I am not an Excel expert, so this may be a question everyone can answer…but me. Sorry)

Posted by Jay on September 15, 2001 5:54 PM

Put the following in A5;


"" is two quote marks with nothing between them.

The equation basically says, if A6 is blank put 'nothing' in A5. If A6 isn't blank, make A5 equal to A4+1.


Posted by Joan on September 16, 2001 7:14 AM

Thanks, Jay! That absolutely worked. I had tried the IF condition, but kept getting "FALSE" back. Your explanation not only helps me with this one, but helps me understand the IF condition better. (you can see how a little info is dangerous.) I am so appreciative, because I am soooo lazy which is what makes me make mistakes and love these safety nets of automation. Do you have any recommended reading/books for learning functions better? We still use Excel 97, if that matters.

Posted by Jay on September 16, 2001 7:30 AM

In response to...

"Do you have any recommended reading/books for learning functions better? We still use Excel 97, if that matters."

There are lots of books about Excel. My favorites are by John Walkenbach. See;


Posted by Aladin Akyurek on September 16, 2001 8:08 AM


Just curious: Must I conclude that you do not intend to copy down the formula Jay suggested?


Posted by Joan L. on September 16, 2001 3:08 PM

Not at all! I have already incorporated it. But also, I now understand what I was doing wrong and I'll probably be able to figure out more of them myself. This is the second "problem" I have posted here, with great results. YOU answered the other one! I am so grateful but recognize that a book or a class might make me less confounded! Regards from Sunny Sherman Oaks CA