Hello. I am struggling with a function I wish to implement but cannot get the desired results. I was hoping someone here could help me. I have uploaded a JPG showing a screenshot of the spreadsheet I am trying to work with.
What I am trying to accomplish is to show the next available Line Sequence Number for a size listed at the top of columns Q through Z. The next available sequence is based on the Service Column set to UNK and then looks at the highest Line Sequence Number (Column V) based on Line Size (Column T).
My first issue was the Line Sequence Numbers in Column V. They are formatted as TEXT in order to keep the leading zeros. I have read that the MIN/MAX function will not work with numbers in cells formatted as text.
Therefore, I changed the format for the cells in Column V to "Special" and entered 000 to keep the 3 digits and the leading zeros if needed.
Unfortunately, when I do that, the name in the "QA Check" cell (Column AD) will show the number as "1" rather than "001". (See last entry)
Questions
1. Is there a way to keep the numbers with leading zeros in Column V so that the Min/Max function will work AND still show the leading zeros for the number in Column AD?
2. Can anyone help with the MAX formula that will go in the cells above showing the next line sequence number?
I've been working on this for a couple of days now so any help is greatly appreciated.
Thank you in advance.
MCJ
Bookmarks