+ Reply to Thread
Results 1 to 5 of 5

Number items in a list that are NOT chosen

  1. #1
    Registered User
    Join Date
    09-01-2019
    Location
    Singapore
    MS-Off Ver
    Office 365 ProPlus
    Posts
    23

    Number items in a list that are NOT chosen

    I have a list of names. For each name in the list, the cell to the left of the name will either be blank, or marked with an "X". In the column to the right of the list, I need a formula that will number the names in order starting with "1" at the top, but will EXCLUDE all of the names with "X" in the cell to the left. The result is that when I "choose" a name from the list by marking it with an "X", the name will be removed from the rankings.

    For example, my top 5 names in the list will have numbers 1-5 marked in the column to the right, but when I put an "X" beside the 3rd name in the list, the cell to the right of the 3rd name will become blank, and the 4th name will then take the number 3, and all names below that will have their numbers change in order from 4 to the end of the list.

    I'm attaching a file that shows what I want the result to look like, but I need a formula to make it work.

    Thanks.
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,460

    Re: Number items in a list that are NOT chosen

    In D2 copied down:

    =IF(B2="",COUNTIF(B$2:B2,"<>X"),"")
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,460

    Re: Number items in a list that are NOT chosen

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  4. #4
    Registered User
    Join Date
    09-01-2019
    Location
    Singapore
    MS-Off Ver
    Office 365 ProPlus
    Posts
    23

    Re: Number items in a list that are NOT chosen

    This worked! Thank you!

    I decided to add some complexity to this, which lead me to modify your formula to :

    =IF(B2="",COUNTBLANK($B$2:B2),"")

    This is because I changed the criteria from "X" to any number, so I needed the formula to simply count the blank cells.

    Now have another question that I'm hoping can be solved with a simple formula rather than a bunch of nested vlookups:

    I've created a sheet called "Names" where I have created 5 separate lists of names. Each name is unique (no duplicate names). On my sheet named "Development," I want column B to search the entire "Names" sheet for the name in Column C and return the data in the "Order #" column (to the right of the name) from the "Names" tab (or an blank cell if Order # is blank).

    Thanks.
    Attached Files Attached Files

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,460

    Re: Number items in a list that are NOT chosen

    You will need to start a new thread for this new query with an appropriate title, please. This thread will be marked as solved.

    No help to be given for this query in this thread.

+ 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. Listbox, Need to change number of items in List (not the items)
    By Kalithro in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-14-2013, 09:23 PM
  2. [SOLVED] Form items chosen in Listbox to populate specific cells in spreadsheet
    By floydian in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 08-14-2013, 07:48 AM
  3. Replies: 12
    Last Post: 02-08-2013, 02:26 PM
  4. Replies: 10
    Last Post: 01-05-2013, 12:31 PM
  5. Replies: 0
    Last Post: 08-21-2012, 11:35 PM
  6. HELP?! Divide a sum by number if items in list
    By jonnykanzen in forum Excel General
    Replies: 3
    Last Post: 10-02-2008, 05:21 AM
  7. How do I refer to the items in a combobox by their number in the list of items?
    By lottesfog in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-06-2007, 10:03 PM
  8. [SOLVED] Looking up several number-master list with different items
    By damorrison in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-14-2006, 08:35 PM

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