HELP REQUIRED WITH DMAX 1

[harrisharris]

I am trying to use DMAX in an inventory spreadsheet to allow me to see when a part number was last issued. The sheet consists of a random column of part numbers (some duplicated), the next column shows the dates that they were issued. The next part of the sheet has a master column of the part numbers and a column next to it where I require the last issue date to be shown. I have used DMAX and this works fine for the first part number but the result is applied to all other part numbers. Any help on this would be much appreciated.

 

[Zathras]

DMAX requres a criteria array to identify which part number you want the max date for. I'm just guessing, but it sounds like you have only one criteria range defined which provides the correct results for only one part number.

There are three things you could do:

1. Create a separate criteria range for each part number. This is probably impractical since you likely have hundreds of part numbers.

2. Use a data table so that the criteria are properly set up for each part number. This is probably unfamiliar to you and would have a bit of a learning curve. It is most likely the best solution however, and is worth learning how to use. (See menu: Data/Table... and the help file for how to use.)

3. Use array formulas. This may be a good fit depending on how many part numbers you have. Recalc time may become a problem. (data tables only recalc when you need them to.)

Here is a demo spreadsheet using array formulas:

A1: 'PARTNO
A2: 1
A3: 2
A4: 3
A5: 2
A6: 3
A7: 3
A8: 2
A9: 2
A10: 1
A11: 2
A12: 3
B1: 'ISSUEDATE
B2: 1/1/03
B3: 2/1/03
B4: 1/31/03
B5: 2/4/03
B6: 1/29/03
B7: 1/28/03
B8: 1/27/03
B9: 1/26/03
B10: 1/25/03
B11: 1/24/03
B12: 1/23/03
D1: 'PARTNUMBER
D2: 1
D3: 2
D4: 3
E1: LASTISSUEDATE
E2: =MAX(IF($A$2:$A$12=D2,$B$2:$B$12))
E3: =MAX(IF($A$2:$A$12=D3,$B$2:$B$12))
E4: =MAX(IF($A$2:$A$12=D4,$B$2:$B$12))

The formulas in E2, E3, E4 must be entered as "array formulas" They must be entered in a special way: Do not simply press "Enter" Instead, press Ctrl+Shift+Enter. If you do it right, you will see the formula bracketed by left and right "curly braces"

 

[Zathras]

To see how to use the Data Table approach, continue with the previous setup and enter the following in the indicated cells:

G1: 'Criteria
G2: 'PARTNO
H1: 'Part Number
H3: 1
H4: 2
H5: 3
I1: 'Last Issue Date
I2: =DMAX($A$1:$B$12,2,$G$2:$G$3)

The text in G2 must match exactly the text in A1.

Select the range

H2:I5

Select Data/Table... from the menu
Enter

G3

for the "Column input cell:"
(Ignore "Row input cell:&quot
Click Ok.
You should see the results appear in I3 thru I5.

 

[harrisharris]

Many thanks Zathras, both methods worked like a dream. You are quite correct, the data table will be a bit of a learning curve but it is something I was not aware of and definitely needs further investigation. Thanks again.