How can I do this?

M

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.
 
M

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?
 
R

Ron Rosenfeld

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.
Click to expand...


An example:

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

Enter text in B1.




--ron
 
P

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
 
E

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
 
M

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
 
R

Ron Rosenfeld

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?
Click to expand...

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
 
P

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
J
 
Top