I have a spreadsheet with our clients on them, with about 5 columns across the top (name, address, phone #'s, email, comments) and in Column A, I have them numbered. The issue is that they are not all the same number of lines apart. For instance if a client has 3-4 different emails or contact #'s, his info takes up 4 lines vs someone that may only take up 1 line with 1 phone # and email, so the numbers are not all evenly spaced out in Column A. The issue is when I add a new client, then the numbers are all off and I have to manually go in and from the new client on, renumber them.

Is there a way to have the numbers automatically renumber in order without me having to manually go in and renumber? If I put a "dummy" number in to let the column know that THIS is where a number should go to, would that work somehow?

Thanks.

I'd argue that this data would best be kept in a database such as Access, but that would be off-topic. One thing that can be done in Excel is have the number calculated based on the presence of information in other columns. The attached spreadsheet illustrates this using the COUNTA function. The formula in the cells in column A check for contents in the column B cells and return an empty string ("") or a count of non empty cells in column B to the current row followed by a period.

I would also argue that this is a database not a spreadsheet. What I don't understand is why you need to sequentially number your clients in a spreadsheet? If you were using a relational database I can understand the need to identify each client with a number. But I don't see the need to do so in a flat file table.

Scleros, when I copied the formula, I pressed the Fx (Insert function key) to the left of the formula bar. I then typed in COUNTA in the Search for a Function box. In the box where it says Value 1, is this where I paste the formula? I tried that and it did not work so I am guessing this is not where it goes.

Scott, the reason I want them numbered is so that we can easily keep a count on how many clients we have by just looking at the last number. This is for several reasons - ordering thank you cards, marketing material, etc.

I am not familiar with Access but if you think I can copy and paste this information over and it would be easier to use, I could give this a try.

Thanks for all your help!

Do you have Access? It won't be as easy as a copy paste if your spreadsheet is set up as I think it is (with multiple rows per client). But I think it would be worth the effort to do.If you want more help with that let us know.

As for getting a count, that should be easy. If your structure is like I think (see attachment) Then a CountIf function {=CountIF(range,"*")} will count the cells in the name column that has the company name.

Quote:

---

Originally Posted by ginarwoodlee

is this where I paste the formula? I tried that and it did not work so I am guessing this is not where it goes.

---

It might be simpler to write down the formula in column A of the sample workbook and then type it in your workbook in the numbering cell of your first contact. The row and column references in the formula may need to be adjusted to correspond with your workbook.