Question

问题:

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仍然是FOO_1,根据A1的价值,在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..

Answer 1

你的做法问题是,A1总是会改变,以反映联通邮局,你一定会利用西非经货联盟的价值。别的是,如果你不像“JMax”所建议的那样在毗邻的牢房里使用该数字,那么他又怎么会进入A栏的其余牢房?

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.

这就意味着,除非在作出改变之前先删除C的价值,否则该邻近的牢房将是错误的。课程如果您有4个联络处,然后删除1个联络处,4个联络处,如果你改变第4个联络处,首先删除联络处4个。如果出于某种其他原因,煽动仇恨和仇恨行为与实际计算相称,我不会为此而依赖。

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

接着说:

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

Answer 2

如果你想要坚持公式解决办法,你可以将A1方案改为:

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

当你拖走和放弃你的公式时,这样做就行了。

友情链接