How can I do this?

[mevetts]

Hi,

Does anyone know of a formula that essentially looks to the cell next to it and if there's text in it put a number in. The numer needs to be one more than the one above it.

So the first in the list will place a 1, then in the next row the number would be 2, then 3 and so on. But only if there is text in the cell next to it.

Essentially I want a numbered list.

Can this be done?

Many thanks,

Mark.

[pinmaster]

Hi, how about:

=IF(B1>"",COUNTA($B$1:B1),"")

HTH
JG

[mevetts]

Hi,

It is recognising if there's text or not, but it just keeps putting a 0 in the cell rather than starting at 1 and then going up by 1 each time, ie. 1,2,3,4 etc...

Any ideas?

[Ron Rosenfeld]

On Mon, 19 Dec 2005 16:59:47 -0600, mevetts
<[email protected]> wrote:

>
>Hi,
>
>Does anyone know of a formula that essentially looks to the cell next
>to it and if there's text in it put a number in. The numer needs to be
>one more than the one above it.
>
>So the first in the list will place a 1, then in the next row the
>number would be 2, then 3 and so on. But only if there is text in the
>cell next to it.
>
>Essentially I want a numbered list.
>
>Can this be done?
>
>Many thanks,
>
>Mark.


An example:

A1: =IF(ISTEXT(B1),ROW(),"")

Enter text in B1.




--ron

[pinmaster]

Hi, did u ajust the formula to your needs?

=IF(B1>"",COUNTA($B$1:B1),"")

or

=IF(B1="","",COUNTA($B$1:B1)

where row 1 is the first row and column B is your text column?

JG

[mevetts]

That doesn't seem to work Ron, being new to formulas I'm not too sure why?

[Elkar]

I think this might be what you're looking for:

=IF(ISTEXT(B2),COUNT($A$1:$A1)+1,"")

This assumes that your count is in Column A, your text is in Column B, and
Row 1 is a header row or blank.

HTH,
Elkar

"mevetts" wrote:

>
> Hi,
>
> Does anyone know of a formula that essentially looks to the cell next
> to it and if there's text in it put a number in. The numer needs to be
> one more than the one above it.
>
> So the first in the list will place a 1, then in the next row the
> number would be 2, then 3 and so on. But only if there is text in the
> cell next to it.
>
> Essentially I want a numbered list.
>
> Can this be done?
>
> Many thanks,
>
> Mark.
>
>
> --
> mevetts
>
>
> ------------------------------------------------------------------------
> mevetts's Profile: http://www.excelforum.com/member.php...o&userid=29130
> View this thread: http://www.excelforum.com/showthread...hreadid=494746
>
>

[mevetts]

It's still not working.

I am beginning on row 58 using cols A and B.

So the formula goes in col A and the text will go in col B.

Does this help?

[pinmaster]

Ok....then:

=IF(B58="","",COUNTA($B$58:B58))
copied down

Regards
JG

[mevetts]

Wahey!!

Thanks so much for your help....and patience!

All the best.

[pinmaster]

You're welcome and thanks for the feedback!

Regards!
JG

[Ron Rosenfeld]

On Mon, 19 Dec 2005 17:46:25 -0600, mevetts
<[email protected]> wrote:

>
>It's still not working.
>
>I am beginning on row 58 using cols A and B.
>
>So the formula goes in col A and the text will go in col B.
>
>Does this help?

I specified the formula to start in A1.

If you start in some other row, you need to adjust it so the count reflects
that row. For example, starting in row 58, the formula would be:

=IF(ISTEXT(B58),ROW()-57,"")




--ron

[pinmaster]

Other versions of Ron's formula:

=IF(ISTEXT(B58),ROW(A1),"")
=IF(ISTEXT(B58),ROW(1:1),"")

unfortunetly these will not account for skipped rows.

Regards
JG