Text and Formula in the Same Cell

[Guest]

Hi all!

Is it possible to place text and a formula in the same cell? I have a column
of people's names. But I would like to add a count of occurences of that name
in the column. I have successfully been able to do the count in another cell
but I want the name and the figure in the same cell.

 

[Tom Ogilvy]

="ABCD " & countif(A1:A10,"ABCD")

 

[Myrna Larson]

I can come up with a couple of formulas to get the name plus its count in
column A. Assuming D1 has the formula to get the count, and you put this
formula is in column A,

="Myrna Larson"&" "&TEXT(D1,"##0")

The formula for D1 is: =COUNTIF($A:$A,"Myrna Larson"&"*")

You can combine the two formulas to get, in column A,

="Myrna Larson"&" "&TEXT(COUNTIF($A:$A,"Myrna Larson"&"*"))

Problem is, both of those create a circular reference. I suppose you could
turn iterations on, but I sure wouldn't do that. Then you get no warning when
you create an unintentional circular reference.

Put the count in a separate column.

 

[Myrna Larson]

But that formula can't go in A1:A10, right? I thought that's what he meant by
"in the column", but maybe I'm over-interpreting....

 

[Guest]

If I did turn on iterations, would it affect all workbooks or just the one
that I turn it on for? And how would I turn it on?

I only need to create this one formula, so I'm not too worried about more
circular references in it, but if the change will affect other workbooks,
then, you're right; I had better not.

 

[Myrna Larson]

There's an error in my 2nd attempt. Should be

="Myrna Larson"&" "&TEXT(COUNTIF($A:$A,"Myrna Larson"&"*"),"0")

I don't remember off-hand whether it affects all open workbooks or not. Try it
and find out. You turn it on under Tools/Options/Calculation. Set the number
of iterations to 1.

Why do you want to do this? Is it for cosmetic reasons for a print-out? If so,
if the full list of names is in column A, you have a list of the unique names
in column B, in C the formula =COUNTIF($A:$A,B1) and in column D the formula
=B1&TEXT(C1,"0"). Set the print area to exclude columna A:C, or hide those
columns before printing.

 

[Tom Ogilvy]

If you want it to count itself, then you would need to click on iteration in
calculate tab of Tools=>options to allow circular references. The formula
would then be

="ABCD " & countif(A1:A10,"ABCD*")

in that case.

Iteration is an application level setting. If it is on, it is on for all
workbooks.