+ 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
    3

    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,104

    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
    Valued Forum Contributor dosydos's Avatar
    Join Date
    12-09-2015
    Location
    Springfield, MA
    MS-Off Ver
    2016
    Posts
    581

    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
    2010
    Posts
    4,332

    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,104

    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
    3

    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,104

    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
    3

    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)

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