# Auto Number Based Off Data From Another Cell

1. ## 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. ## Re: Auto Number Based Off Data From Another Cell

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

Does that work for you?

3. ## Re: Auto Number Based Off Data From Another Cell

EDIT: I like my answer below better.

4. ## 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:
`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. ## 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:
`Please Login or Register  to view this content.`

6. ## 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))))

7. ## 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. ## 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. ## Re: Auto Number Based Off Data From Another Cell

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

There are currently 1 users browsing this thread. (0 members and 1 guests)