Jul 02 2023 06:48 AM
Jul 02 2023 06:48 AM
I have a spreadsheet with various information on clients. I need excel to automatically recount and enter the correct number when I remove a row. What is the correct formula to enter?
Jul 02 2023 08:15 PM
"I need excel to automatically recount and enter the correct number when I remove a row."
A formula in a cell will (by default) be recalculated and the resulting value will be stored in that cell whenever the workbook is opened, and whenever Excel determines that a cell/range that the cell's formula depends upon has changed. So the "enter the correct number" part is probably no problem.
Your use of the term "spreadsheet" suggests that these rows of client info are all on one worksheet. But what specifically do you want the recount to count? A good way to decide: Which column is always populated for a (non-empty) data row? (If there are multiple such columns, choose one.) Are the data values in this column all numbers, all textual information, or a mix?
If this data is in an Excel table, give us the table name and the column name. If this data is not in an Excel table (i.e., it's just a rectangular range of data), give us the column letter and the first potential row number of data (essentially, how many rows to ignore because they contain page and/or column headers or just blank rows you want to keep).
Jul 03 2023 05:33 AM
Jul 03 2023 09:34 AM
That's better, but I can't tell from your description whether column A contains your assigned client number or some other (possibly text) value. Here are formulas for the two possibilities:
=COUNT($A$385:$A$15384) …if column A contains numbers, such as your client number =COUNTA($A$385:$A$15384) …if column A contains text, or mixed text and numbers
Those assume that you have a maximum of 15,000 client rows. It's trivial to increase the end of the range if you feel you may have more rows.
But perhaps Excel could compare the rows for you. Is the client number present in both sources? Or are the client names unique? Or is there another pair of data columns whose contents match between your two sources, aside from any deleted rows?
To demonstrate, let me suppose that column C in your first worksheet contains a unique client identifier that is also present in column D in your other source, which I will assume to be Other Sheet in the same workbook. (This method also works if the other source is in a different workbook, but it's a little messier.) You can use a helper column in your first worksheet—I will use the column X—to hold an indicator of whether that client is matched on Other Sheet. See the attached workbook.