Formula to generate ref nr

[lizemi]

Hi
I need some help on a formula to generate a reference number.
Basically, I need a formula that looks if there is a value other than 0 in collum A and then creates a reference number in collum A that is 1+ the previous value.
So if there is a value that is not 0 in D3 A3 has to have a reference number like A3 has FEB001
If for example the value in D is 0 like in D5 A should be empty like A5 is
But if Example D5 had a value of not 0 then A5 should be FEB003 and A14 will then be FEB004 and so on

 

[SunnyAlv]

Like this ?

=IF(D3=0,"",CONCAT("LMFEB",TEXT(COUNT($D$3:D3),"000")))

 

[lizemi]

Like this ?

=IF(D3=0,"",CONCAT("LMFEB",TEXT(COUNT($D$3:D3),"000")))

Like this ?

=IF(D3=0,"",CONCAT("LMFEB",TEXT(COUNT($D$3:D3),"000")))

View attachment 107943

View attachment 107943

Hi Thanks
The formulae gives me a blank I need yes and does give a reference nr when not 0
The reference nr however does not follow sequence. EG A14 generated LMFEB012 not LMFEB003

 

[SunnyAlv]

try like this

=IF(IFERROR(OR(D3=0,D3="-",AND(D3<1,D3>-1)),""),"",CONCAT("LMFEB",TEXT(COUNT($D$3:D3),"000")))

 

[Peter_SSs]

In A3, copied down try

=IF(D3=0,"","LMFEB"&TEXT(COUNTIF(D$3:D3,">0"),"000"))

 

[lizemi]

In A3, copied down try

=IF(D3=0,"","LMFEB"&TEXT(COUNTIF(D$3:D3,">0"),"000"))

Thanks so much it works perfectly

 

[Peter_SSs]

Thanks so much it works perfectly

You're welcome. Thanks for the follow-up.