increment & auto populate cell value based on another cell and previous populated value

1. increment & auto populate cell value based on another cell and previous populated value

In column A I want to auto populate a series of ID numbers in ascending order. I only want this to happen if a text value is present in the corresponding row in column B.
For example:

If B1 contains text, enter 1 in A1
If B4 contains text, enter 2 in A4
If B8 contains text, enter 3 in A8 .... And so on

Each consecutive row in column B will not have a value which means not every cell in column A should be populated

For example

If cells B1, B4, B8, B15 all contain text, I only want to auto populate Cells A1, A4, A8, A15 with the values 1,2,3,4

I have tried writing the following formula in cell A1:

=If(B1=””,””,1)

This works for cell A1 but if I drag the formula down, all cells in column A which have a corresponding text value in column B display the number 1.

How can I amend this formula to reference the value previously entered in cell A(x) and increment it by 1 each time?

Many Thanks

2. Re: increment & auto populate cell value based on another cell and previous populated valu

Hi Rob
If you Use "COUNT" then it would work as follows:
=IF(B4="","",COUNT(A1:A3)+1)
=IF(B5="","",COUNT(A1:A4)+1)
=IF(B6="","",COUNT(A1:A5)+1) etc.

I believe this is what you are after.

3. Re: increment & auto populate cell value based on another cell and previous populated valu

As I have been reminded, it should be written as follows to save you typeing the code on every line.

=IF(B2="","",COUNT(A\$1:A1)+1)
=IF(B3="","",COUNT(A\$1:A2)+1) etc

4. Re: increment & auto populate cell value based on another cell and previous populated valu

Another slightly different way.

 Row\Col A B C 1 1 George In A1 and filled down: =IF(B1="","",COUNTIF(\$B\$1:B1,"<>")) 2 3 4 2 Alice 5 6 7 8 3 Mark 9 10 11 12 13 14 15 4 Mary

5. Re: increment & auto populate cell value based on another cell and previous populated valu

Nice one guys

Thanks to both of you for your help!

6. Re: increment & auto populate cell value based on another cell and previous populated valu

You're welcome. Thanks for the feedback and the rep.

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1