Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Formula to increment sequence only if text in a particular cell
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Formula to increment sequence only if text in a particular cell
On Tue, 7 Jul 2009 16:48:01 -0700, Mills
wrote: 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 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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Formula to increment sequence only if text in a particular cel
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!!! :) "Ron Rosenfeld" wrote: On Tue, 7 Jul 2009 16:48:01 -0700, Mills wrote: 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 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 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Formula to increment sequence only if text in a particular cel
On Tue, 7 Jul 2009 17:42:08 -0700, Mills
wrote: 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!!! :) 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 |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Formula to increment sequence only if text in a particular cel
you can literally hear the penny drop in my head, I got it :)
again thanks for taking the time to answer!! :) "Ron Rosenfeld" wrote: On Tue, 7 Jul 2009 17:42:08 -0700, Mills wrote: 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!!! :) 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 |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Formula to increment sequence only if text in a particular cel
On Tue, 7 Jul 2009 18:02:11 -0700, Mills
wrote: you can literally hear the penny drop in my head, I got it :) again thanks for taking the time to answer!! :) You're welcome. Thanks for letting me know my explanation made sense. (Sometimes my wife tells me I'm hard to understand :-)) --ron |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Formula to increment sequence only if text in a particular cell
Hi,
here is a slightly shorter variation '=IF(B1="","","Test_"&TEXT(COUNTA(B$1:B1),"000")) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Mills" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
increment cell reference in a formula | Excel Worksheet Functions | |||
Need formula to increment cell reference every 8th row | Excel Discussion (Misc queries) | |||
Capturing a word sequence as part of a text in a spreadsheet cell | Excel Worksheet Functions | |||
how to auto increment cell location within formula | Excel Worksheet Functions | |||
Formula for copying a sequence for every 8th cell | Excel Worksheet Functions |