Generate customized sequential numbers with if formula

Sissi

New Member
Joined
Feb 17, 2018
Messages
2
Hello,

I have a sheet with some data that populate based on some formulas and i want to create a column that will be used for unique IDs (ideally incrementing) everytime new data is entered.

The IDs should be created thus:
=if(a1=“51321”,(char84)& “the auto generated number”, if(a1=53202,(char54)& “the next auto generated number”, “”).

Can anyone help with this, please?
I know a macro will be the ideal but i am not too familiar with the types of coding.

Thanks
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

JonXL

Active Member
Joined
Feb 5, 2018
Messages
357
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
How's this?

Code:
=IF(A1=51321,CHAR(84)&ROW(),IF(A1=53202,CHAR(54)&ROW(),""))
 
Last edited:

Sissi

New Member
Joined
Feb 17, 2018
Messages
2
How's this?

Code:
=IF(A1=51321,CHAR(84)&ROW(),IF(A1=53202,CHAR(54)&ROW(),""))

Hi JonXL and thanks a lot for your reply,

I did try and the cells increment but I guess what I omitted in my previous post is that if there are empty cells between the results, I would like the next cell with a result to pick-up from the last number.
Right now, with this formula, the first couple of rows are like this:

T7
T8
Result in next cell is ""
Result in next cell is ""
T11
T12
T13
Result in next cell is ""
Result in next cell is ""
H16

I would like that after the empty cell, let's say for the result in T11, it should actually be T9, an increment of the last cell with a data. Same thing for the results between T13 and H16.

I hope my explanation makes sense.

Thanks a lot.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,336
Messages
5,528,105
Members
409,802
Latest member
joeino

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top