Results 1 to 6 of 6
  1. #1
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Incrementing a row value (97--->)

    I have created a workbook that has a worksheet called Projects

    In column A of this sheet I have placed the values 1,2,3--->200. This reference is used in other sheets to find a value in column B using the Index Formula.

    What I would like to do is have the user type the name of a new project in column B and then have the cell next to it in Column A update and increase by 1 from the one above it. I can do this if I use =if(B2="","",A1+1), this is all very well but if a project no longer exists and the row is deleted all the cells below it show#REF.

    Can anyone tell me a good way of providing a way of doing increments ( I would prefer it not to be VBA/Macro) in a formula.
    Jerry

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Incrementing a row value (97--->)

    Try this:
    Select A2
    Insert name - define
    Name: <pre>Increment</pre>

    refers to:
    <pre>=if(B2="","",A1+1)</pre>

    <ok>

    In A2 enter the formula:
    <pre>=Increment</pre>

    Copy this formula down the column

    Steve

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Incrementing a row value (97--->)

    OK

    Wasted your time, sorry.

    A colleague has just come up with this! Works fine for the job unless you can think of another idea
    Jerry

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Incrementing a row value (97--->)

    Thanks Steve

    You beat me to the line!! This looks better than my solution, I think I will use this instead
    Jerry

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Incrementing a row value (97--->)

    If the items are contiguous, then you could also use:
    <pre>=IF(B2="","",ROW()-1)</pre>


    I would think that this would be faster for large lists, since it does not involve actually counting the items in the list

    Steve

  6. #6
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Incrementing a row value (97--->)

    Thanks Steve

    Somewhat shorter than my original idea.

    =IF(B2="","",ROW()-1)

    It is and works great
    Jerry

Posting Permissions

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