+ Reply to Thread
Results 1 to 6 of 6

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

  1. #1
    Registered User
    Join Date
    09-19-2016
    Location
    london
    MS-Off Ver
    2007
    Posts
    5

    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. #2
    Registered User
    Join Date
    10-31-2011
    Location
    Kent, England
    MS-Off Ver
    Excel 2010
    Posts
    43

    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. #3
    Registered User
    Join Date
    10-31-2011
    Location
    Kent, England
    MS-Off Ver
    Excel 2010
    Posts
    43

    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. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 Version 2301, rarely Office 2007
    Posts
    12,887

    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
    Dave

  5. #5
    Registered User
    Join Date
    09-19-2016
    Location
    london
    MS-Off Ver
    2007
    Posts
    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. #6
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 Version 2301, rarely Office 2007
    Posts
    12,887

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

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Auto populate cells with number according to previous cell
    By kcarth in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-29-2015, 01:19 PM
  2. Auto populate next cell depending on previous column
    By ngocpdn in forum Excel General
    Replies: 3
    Last Post: 01-09-2015, 01:08 AM
  3. Macro - how to auto populate the data, based on the previous cell values
    By Avinash Kumar in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 09-21-2012, 02:03 PM
  4. Auto Populate Cells Dependent on Information in Previous Cell
    By senescu in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 07-21-2010, 11:50 AM
  5. Replies: 2
    Last Post: 07-21-2010, 09:10 AM
  6. Macro to auto populate the destination cell based on the values of the previous cell
    By Avinash Kumar in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-05-2009, 02:06 AM
  7. Macro - auto populate the values based on previous cell values
    By Avinash Kumar in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-05-2009, 02:02 AM

Bookmarks

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