Formula to increment sequence only if text in a particular cell

  • Thread starter Thread starter Mills
  • Start date Start date
M

Mills

Hi,

I'm new to this, and not sure how to do this, I'd like to increment a field
by one only if there's text in a particular cell. This is what I have, so
I'm trying to write a formula that automatically populates column A...this is
what I have tried
=IF(C4<>"","Test_"&TEXT(ROW(A1),"000"), "") --- but this formula
increments even when no text in B, so I end up with a value of Test_003 in
A3, where I need to have Test_002
A B
1 Test_001 some text
2
3 Test_002 more text

Any advice would be greatly appreciated!! :)
Cheers,
Mills
 
Hi,

I'm new to this, and not sure how to do this, I'd like to increment a field
by one only if there's text in a particular cell. This is what I have, so
I'm trying to write a formula that automatically populates column A...this is
what I have tried
=IF(C4<>"","Test_"&TEXT(ROW(A1),"000"), "") --- but this formula
increments even when no text in B, so I end up with a value of Test_003 in
A3, where I need to have Test_002
A B
1 Test_001 some text
2
3 Test_002 more text

Any advice would be greatly appreciated!! :)
Cheers,
Mills
Click to expand...

I'm not sure what the contents of C4 has to do with anything. Assuming that is
irrelevant

1. With your formula(s) starting in A1 and the cells to be tested starting in
B1.

A1: =IF(B1="","",TEXT(COUNTA($B$1:B1),"""Test_""000"))

Select A1 and fill down as far as required.

--ron
 
sorry C4, was in my formula, I forgot to edit it for the post.
Not exactly sure how this works, but this works great!!
Thanks heaps and for the quick reply!!! :)
 
sorry C4, was in my formula, I forgot to edit it for the post.
Not exactly sure how this works, but this works great!!
Thanks heaps and for the quick reply!!! :)
Click to expand...

Glad to help. Thanks for the feedback.

AS to "how it works", look at what happens to the COUNTA($B$1:B1) segment as
you fill down column A.
--ron
 
you can literally hear the penny drop in my head, I got it :)
again thanks for taking the time to answer!! :)
 
you can literally hear the penny drop in my head, I got it :)
again thanks for taking the time to answer!! :)
Click to expand...

You're welcome. Thanks for letting me know my explanation made sense.
(Sometimes my wife tells me I'm hard to understand :-))
--ron
 
Back
Top