Max function with conditions

[Guest]

I have a project numbers which is split up over two columns. The first 4
digits (Col B) is the number for the parent project and the next 3 digits (C)
is the subproject number for that project.

We anticipate this spreadsheet being large so I would like to create a
formula at the top of the spreadsheet that shows the next available number so
that the user does not have to search. Finding the next parent project is
easy (=MAX(B#:B#) +1) but what I need to do is have the user enter a parent
project number (cell A4) and the formula showsl him the next available
subproject number for that parent project. Any ideas.

Eg. the user enters 0054 as a parent project (A4) and the formula would
display 005 which would be the last subproject for project 0054.

 

[Guest]

See if this Array Formula works for you:

=MAX(IF(B2:B1000=D1,C1:C1000,0))+1

Note: Array formulas are entered with CTRL-SHIFT-ENTER instead of just
Enter. If done properly, the formula should be enclosed in { }.

This assumes your (MAX(B#:B#)+1 formula is in cell D1. Change to reflect
your actual cell reference.

HTH,
Elkar

 

[Guest]

I'm not sure what I'm doing wrong. In D1 is the data entry place where the
user will type the parent project number that needs a sub-project added. I
entered 0007 into this field as it is a known parent project that exists on
the spreadsheet.

I place this formula into the correct cell (I removed the "+1" portion to
see if the formula would give me the max number first - then I would have
added this piece) as an array I get #N/A and if I enter it without the array
key sequence I get the MAX number in that column (C) which happens to belong
to another parent project and NOT 0007. Is there something I'm missing.

 

[Guest]

The problem might be with cell formatting. Since your projects have leading
zeroes, are they stored as Text values, or is the number formatted to display
leading zeroes? If one set is Text and the other number, then it's Apples
and Oranges as far as Excel is concerned.

If you're not sure about the cell formatting, then try enclosing the B and D
column references in VALUE functions.

=MAX(IF(VALUE(B2:B1000)=VALUE(D1),C1:C1000,0))+1

Note: this is still entered as an Array (CTRL-SHIFT-ENTER)

This ensures that numbers are compared to numbers regardless of how they are
stored.

HTH,
Elkar

 

[Sandy Mann]

Elkar,

You have a typo in both formulas whereby you are referencing B2:B1000 but
C1:C1000.

Assuming that Column C is numeric (otherwise +1 will throw an error) then
simply:

=MAX((B2:B1000=D1)*(C2:C1000))+1

Still array entered, works for me.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Guest]

Workds perfectly! Thanks so much for your help. I thought I formatted them
the same as they looked the same but they weren't Formatting will get you
every time huh? Thanks again!