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

Hi, how about:

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

HTH
JG


--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
View this thread: http://www.excelforum.com/showthread...hreadid=494746

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?


--
mevetts


------------------------------------------------------------------------
mevetts's Profile: http://www.excelforum.com/member.php...o&userid=29130
View this thread: http://www.excelforum.com/showthread...hreadid=494746

On Mon, 19 Dec 2005 16:59:47 -0600, 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.


An example:

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

Enter text in B1.




--ron

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


--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
View this thread: http://www.excelforum.com/showthread...hreadid=494746

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


--
mevetts


------------------------------------------------------------------------
mevetts's Profile: http://www.excelforum.com/member.php...o&userid=29130
View this thread: http://www.excelforum.com/showthread...hreadid=494746

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

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?


--
mevetts


------------------------------------------------------------------------
mevetts's Profile: http://www.excelforum.com/member.php...o&userid=29130
View this thread: http://www.excelforum.com/showthread...hreadid=494746

Ok....then:

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

Regards
JG


--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
View this thread: http://www.excelforum.com/showthread...hreadid=494746

Wahey!! :)

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

All the best.


--
mevetts


------------------------------------------------------------------------
mevetts's Profile: http://www.excelforum.com/member.php...o&userid=29130
View this thread: http://www.excelforum.com/showthread...hreadid=494746

You're welcome and thanks for the feedback!

Regards!
JG


--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
View this thread: http://www.excelforum.com/showthread...hreadid=494746

On Mon, 19 Dec 2005 17:46:25 -0600, mevetts
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

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


--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
View this thread: http://www.excelforum.com/showthread...hreadid=494746