+ Reply to Thread
Results 1 to 11 of 11

Formula to insert number in one column when an item is introduced in another column

  1. #1
    Forum Contributor
    Join Date
    09-13-2016
    Location
    Kenya
    MS-Off Ver
    Excel 2007 & Excel 2010
    Posts
    264

    Formula to insert number in one column when an item is introduced in another column

    In the workbook attached, column C contains some numbering while D contains items being numbered. What would be the formula that would automatically introduce the next number in column C when the next item is introduced in column D?

    Also, is there a way to to push down the total and the average as more items are added without inserting new rows?

    Thank you.

    Auto Numbering.xlsx
    OnditiGK

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,518

    Re: Formula to insert number in one column when an item is introduced in another column

    For C

    in C4

    =C3+1

    is there a way to to push down the total and the average as more items are added without inserting new rows?
    n
    No!

    Logically , moving the total down is equivalent to adding rows.

    The easiest alternative is the place the AVERAGE/TOTAL ABOVE the headings in row 2.
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    09-13-2016
    Location
    Kenya
    MS-Off Ver
    Excel 2007 & Excel 2010
    Posts
    264

    Re: Formula to insert number in one column when an item is introduced in another column

    Thank you john for the quick response. The formula I needed is one that would also delete the number when items in column D are also deleted.

  4. #4
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,129

    Re: Formula to insert number in one column when an item is introduced in another column

    hi there. if you don't mind a slight change in the layout, convert this to a Table. click on C2 and press CTRL + T. press OK. paste this formula in C3:
    =IF([@Item]<>"",ROW()-2,"")
    copy down.

    While selecting the Table, go to the Design Tab. Check on Total Row. that sums up column E. do a dropdown for D8 and choose Average. it shows an error because it is summing up column D (which are texts). change the formula to:
    =SUBTOTAL(101,[Quantity])

    to add rows, press TAB key in the last row of column E (minus the subtotal). right now in my eg file, it's E8.
    Attached Files Attached Files

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,518

    Re: Formula to insert number in one column when an item is introduced in another column

    Try

    in C4

    =COUNTIF($D$4:D4,"?*")

    Copy down

  6. #6
    Forum Contributor
    Join Date
    09-13-2016
    Location
    Kenya
    MS-Off Ver
    Excel 2007 & Excel 2010
    Posts
    264

    Re: Formula to insert number in one column when an item is introduced in another column

    Thank you benishiryo.

    John, your formula is close to what I want. However, deleting items in column D changes the number in that row under column C to the number in the previous row instead of not displaying any number at all.

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,518

    Re: Formula to insert number in one column when an item is introduced in another column

    So you want to retain original numbers: perhaps you should be more specific in defining your requirements!

    I assume you have an answer from benishiryo.

  8. #8
    Forum Contributor
    Join Date
    09-13-2016
    Location
    Kenya
    MS-Off Ver
    Excel 2007 & Excel 2010
    Posts
    264

    Re: Formula to insert number in one column when an item is introduced in another column

    Check the workbook below:

    Cells D7:D9 are empty but 4 is displayed in cells C7:C9. I want a situation where if for example cells D7:D9 are empty, then no number should be displayed in cells C7:C9. The numbers should only be displayed if there is data in cells D7:D9.

    Auto Numbering.xlsx

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,495

    Re: Formula to insert number in one column when an item is introduced in another column

    Try this:

    =IF(D3="","",COUNTIF($D$3:D3,"?*"))

    You ought to be familiar with basic IF statements that test whether or not a cell is blank by now ...
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  10. #10
    Forum Contributor
    Join Date
    09-13-2016
    Location
    Kenya
    MS-Off Ver
    Excel 2007 & Excel 2010
    Posts
    264

    Re: Formula to insert number in one column when an item is introduced in another column

    Thank you AliGW. This is what I needed. Thanks to John and benishiryo too.

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,495

    Re: Formula to insert number in one column when an item is introduced in another column

    You're welcome!

+ 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. Replies: 3
    Last Post: 02-11-2015, 02:47 AM
  2. Replies: 13
    Last Post: 09-19-2013, 06:54 PM
  3. Replies: 1
    Last Post: 06-10-2013, 07:08 PM
  4. Find highest revision number in one column of a item number in another column
    By Tasiast in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-01-2013, 01:03 PM
  5. [SOLVED] Formula to insert number in column if name is chosen on another sheet
    By groundin in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-09-2013, 01:10 PM
  6. Replies: 5
    Last Post: 05-17-2012, 10:51 PM
  7. [SOLVED] Formula to Insert the No 1 in column b when a number is detected in Column A
    By leinster93 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-29-2012, 11:21 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