creating a counter cell and static cells in excel

Problem:

• I have a counter cell: A1 with a value of =COUNTIF(B:B;"FOO")
  • This gives me a current "count" of all instances of "FOO" in column B.
• I have a value cell C1 with a formula of: =IF(B1="FOO";"FOO_" & A1;)
  • This gives me a result of FOO_1 if "FOO" only exists once in Column B

Question:

• I want to be able to reference the value of A1 at the time I write out the contents to the cell of C1. When A1 updates, I do not want C1 to be modified. C2 should now take the update of A1 (which would now 开发者_运维百科be 2) and C2 would be: FOO_2 for example:

A1 = 1 B1 = FOO C1 = FOO_1

A1 = 2 B2 = FOO C2 = FOO_2

C1 still remains FOO_1 based on the value of A1 before a new row was added to column B

Looking for an automated way to create an ID, like an increment value in MySQL, and not looking for a solution that involves a person copy / pasting..

The problem with your approach is that, A1 will always change to reflect the COUNT, you can't use a value that WAS. What else is going into the remaining cells in Column A that you could not use the Count in the adjacent row as @JMax suggests?

If you have to increment with fixed Counts, I would recommend you think of a simple macro on a change event of whichever cell you are wanting. For instance everytime you enter "FOO" or "BAR" or anything, it would do a COUNTIF of the Range in B:B and concatenate the result entering it in the adjacent C. No reason to depend upon A1 at all, and it possibly changing.

This example may get you started. And the Countif may use a ';' for your version of Excel It assumes you are doing data entry and then moving to the next cell down. It also checks whether you have already made an entry in the adjacent cell in C so it does not change the count if you revisit a cell.

Which means it will be wrong in that adjacent cell unless you delete the value in C first before making a change. Of Course If you have 4 FOOs and then delete one you will still have FOO_4 and it won't change so if you change the 4th FOO to BAR first delete FOO_4. If the Increment HAS to match actual counts for some other reason, I would not rely upon this for that.

Sub doIncrement()
 If ActiveCell.Column = 2 And ActiveCell.Offset(-1, 1) = "" Then
 ActiveCell.Offset(-1, 1) = ActiveCell.Offset(-1, 0) & "_" & WorksheetFunction.CountIf(Range("B:B"), (ActiveCell.Offset(-1, 0)))
 Else: Exit Sub 'or do something else
 End If
End Sub

Then call this in

Private Sub Worksheet_Change(ByVal Target As Range)
 Call doIncrement
End Sub

If you want to stick to a formula solution, you can replace the A1 formula by:

=COUNTIF($B$1:B1;"FOO")

That would do the trick when you drag and drop your formula.