+ Reply to Thread
Results 1 to 9 of 9

Auto Number Based Off Data From Another Cell

  1. #1
    Registered User
    Join Date
    03-28-2017
    Location
    Cincinnati, OH
    MS-Off Ver
    2016
    Posts
    9

    Post Auto Number Based Off Data From Another Cell

    Hello,

    I have seen this question posted before, but cannot figure out how to input a formula that works for my spreadsheet. The data is as follows:

    Column C Column D
    Invoice # Employee
    1701767 Michelle
    1701767 Michelle
    1701767 Michelle
    1701768 Erica
    1701768 Erica
    1701769 Malori

    How can I have the invoice numbers in Column C automatically populated based off of the employee name in column D? There are sometimes 3 lines per employee, sometimes just 1 line, or sometimes there are upwards of 8 lines. The formulas I have seen before usually create an invoice number for each line, instead I need a formula that keeps the same invoice number until the employee name changes in Column D.

    Much appreciated!!!

  2. #2
    Forum Contributor
    Join Date
    01-05-2017
    Location
    New York
    MS-Off Ver
    Office 2016
    Posts
    206

    Re: Auto Number Based Off Data From Another Cell

    Put in C8: =IF(D8=D7,C7,C7+1).

    Does that work for you?

  3. #3
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Auto Number Based Off Data From Another Cell

    EDIT: I like my answer below better.
    Last edited by CAntosh; 03-28-2017 at 11:45 AM.

  4. #4
    Forum Expert dosydos's Avatar
    Join Date
    12-09-2015
    Location
    Massachusetts
    MS-Off Ver
    365(PC) V:2202
    Posts
    1,468

    Re: Auto Number Based Off Data From Another Cell

    a simple solution that might work depending how your workbook is setup :
    1) in C1 enter the first invoice number manually.
    2) in C2 enter this formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    this formula sees if the name is the same in the above cell and if so returns the same invoice number.. if it is a different name then the cell directly above it, it makes the next invoice number +1.

  5. #5
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: Auto Number Based Off Data From Another Cell

    here is my understanding

    create a table
    Column E Column F

    Invoice Employee

    1701767 Michelle
    1701768 Malori
    all invoice All names

    then in Column C

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

  6. #6
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Auto Number Based Off Data From Another Cell

    Actually, if they'll always be in order, Raphaelp's solution should do fine. If they might be out of order, try this:

    =IF($D3="","",IF(COUNTIF($D$2:$D2,$D3)=0,MAX($C$2:$C2)+1,INDEX($C$2:$C2,MATCH($D3,$D$2:$D2,0))))
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    03-28-2017
    Location
    Cincinnati, OH
    MS-Off Ver
    2016
    Posts
    9

    Re: Auto Number Based Off Data From Another Cell

    I am having trouble getting these to work. Is there a formula that will calculate all of the numbers automatically so I don't have to put a formula in each cell for each line?

  8. #8
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Auto Number Based Off Data From Another Cell

    I believe that all of the solutions presented above are designed to be filled down. You should only need to the enter the formula in one cell, then fill it down through all of the others. See the link for visuals:

    https://support.office.com/en-us/art...3-83aa1a63e218

    Note: The link specifies Excel 2016 for Mac, but it applies ubiquitously, so ignore that.

  9. #9
    Registered User
    Join Date
    03-28-2017
    Location
    Cincinnati, OH
    MS-Off Ver
    2016
    Posts
    9

    Re: Auto Number Based Off Data From Another Cell

    Thank you very much, this worked like a charm! Have a great day!

+ 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. [SOLVED] Auto Populating a number of cells based on the value of another cell
    By Ceewagon in forum Excel General
    Replies: 6
    Last Post: 09-14-2015, 08:05 AM
  2. Auto fill of data in one cell based on another cell in same sheet
    By goribmama in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-08-2015, 01:28 PM
  3. Replies: 3
    Last Post: 09-26-2014, 01:07 AM
  4. [SOLVED] Auto Replicate data to new rows based on number of chances
    By AriesCastillo in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 02-24-2014, 04:59 AM
  5. [SOLVED] Auto Number based on cell colour
    By turist in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 11-03-2013, 08:28 PM
  6. [SOLVED] auto populate cell data based on specific selection in another cell
    By tinka590 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-07-2013, 02:37 AM
  7. Auto-Converting Cell Value to link & moving rows of data based on cell value
    By Th3Forgotten in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-15-2013, 08:05 AM

Tags for this Thread

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