0

I'm trying to keep the numbers in "Inv.No." till 3-digits only having condition that either of the two specific cells are not empty. I am unable to do that after No.10. Below are few of the codes that I've tried with IFS function. Please note that the all cells have been kept to be "Numerical", the first is [(E5) = "00"&1 ]

Codes: =ifs(OR(I15="",E14=""),"",if(OR(E14>1,E14<9),"00"&(E14+1),"0"&(E14+1)))

=ifs(OR(I11="",E10=""),"",OR(E10>1,E10<9),"00"&(E10+1))

Image of the current ouput

Sanath
  • 3
  • 1

1 Answers1

0

Try using Text instead of the If statement:

=if(E5="","",text(E5,"000"))

Or as an array formula:

=ArrayFormula(if(E5:E="","",text(E5:E,"000")))

enter image description here

Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37
  • 1
    Thank you for your help. It wasn't what I was looking for but thanks to your answer found a way around it. Thank you once again. Cheers :) – Sanath Jan 31 '22 at 14:16