Max if variable begins with
Try this array formula** :
=TEXT(MAX(IF(LEFT(A2:A20)="F",--MID(A2:A20,3,5))),"00")
** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
Assuming the numbers are no more than 5 digits. If they are increase the 5
in the MID function as needed.
--
Biff
Microsoft Excel MVP
"JICDB" wrote in message
...
I don't see anything like this posted already - so here's my dilema.
Contract numbers start with various letters based on the type of contract
they are (F, Q, H, M). They then go in numerical order per letter.
example
of the format: F-04. I want a formula to look at an entire columns of
data
(about 200 rows) and tell me what the last contract number used for that
letter is. For example I have to enter a new contract that needs to start
with a Q. I want the formulas to look in this column and find the largest
Q
contract and add one to it so that I know what the next available number
is.
If this is even possible I'm pretty sure it is an array. Thanks for your
help.
|