Hi all
I am trying to create a reference number automatically using the following formula:
=UPPER(CONCATENATE(LEFT(R2,4),"-",COUNTIF($R$2:R2,R2)))
...where column R contains a name formatted like Smith, John (so the outcome is SMIT-1, then SMIT-2 on second occurrence etc). The names are picked from a list on a Microsoft Form (it's an application process and we need to know who the person is applying for the service from, specifically) and when an MS Form response is received it automatically creates a new line in the table and the reference number is generated.
Unfortunately when a new row is added to the table, upon a second occurrence of any name, it keeps changing the previous occurrence to the new number as well. For instance:
Name Reference
Smith, John SMIT-1
Jones, Tom JONE-2
becomes:
Name Reference
Smith, John SMIT-1
Jones, Tom JONE-2
Jones, Tom JONE-2
Where it should be SMIT-1, JONE-1, then JONE-2. I have checked the formula and it is changing the value of the middle cell (where it should read R3,R3 or R4,R4 below it is reading R4,R3 and R5,R4).
=UPPER(CONCATENATE(LEFT(R3,4),"-",COUNTIF($R$2:R4,R3)))
=UPPER(CONCATENATE(LEFT(R4,4),"-",COUNTIF($R$2:R5,R4)))
I have tested this on a blank Excel worksheet without any formatting (i.e. not in a table) and it seems to work fine. So the automatic creation of a new row in the table is clearly linked to the issue. But I have no clue how to fix.
Hope all of this makes sense! Please let me know if any more info is needed
Any help appreciated
Bookmarks