willow1985
Well-known Member
- Joined
- Jul 24, 2019
- Messages
- 888
- Office Version
- 365
- Platform
- Windows
I have the below formula that finds the next number letter sequence in column A of sheet "Batch Log" depending on which letter is selected in cell J2
For example:
if letter "A" is selected the next sequence from "Batch Log" it would give is A137
if letter "B" is selected, the next sequence the formula would give would be B1847
"C" is C611 and so on
There is one problem, the number next sequence for "D" needs to be D067 however the formula returns D67 and skips the 0.
This formula has been working great for me but I couldn't help but be curious if there was a way to account for the "0" for "D"
Current formula:
=IF(J$2="","",J$2&AGGREGATE(14,6,MID('Batch Log'!A$1:A$10000,2,9)/(LEFT('Batch Log'!A$1:A$10000,1)=J2),1)+1)
Any ideas would be appreciated
Thank you
Carla
For example:
if letter "A" is selected the next sequence from "Batch Log" it would give is A137
if letter "B" is selected, the next sequence the formula would give would be B1847
"C" is C611 and so on
There is one problem, the number next sequence for "D" needs to be D067 however the formula returns D67 and skips the 0.
This formula has been working great for me but I couldn't help but be curious if there was a way to account for the "0" for "D"
Current formula:
=IF(J$2="","",J$2&AGGREGATE(14,6,MID('Batch Log'!A$1:A$10000,2,9)/(LEFT('Batch Log'!A$1:A$10000,1)=J2),1)+1)
Any ideas would be appreciated
Thank you
Carla