Find MAX value by criteria

[MarkBeck]

Hi

I store data in one transaction sheet that pertains to various departments (Dep Column D). Each transaction has a Transaction Number (T/A) (Column A) and keeps increasing by 1. Each department has its own number sequence.

I use an "entry sheet" with multiple transaction lines; when i am done with a batch i PasteSpecial=Values to the main transaction sheet.

My entry sheet needs to lookup the MAX+1 of T/A, based on assesing only the transactions that have the same department name as in the entry sheet.

Then, i need an EventChange part to fire the code every time i populate a department on a new line on the entry sheet.

 

[firefytr]

Hi there,

Can you post some examples here?

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[MarkBeck]

DATASHEET
T/A DATE AMOUNT DEP DETAIL
2364 12/10/2006 2,000.00 100 ABC
2365 13/10/2006 1,500.00 100 CBA
2366 14/10/2006 4,630.00 100 BAC
4867 12/10/2006 2,000.00 50 ABC
4868 13/10/2006 1,500.00 50 CBA
4869 14/10/2006 4,630.00 50 BAC
8467 12/10/2006 2,000.00 20 ABC
8468 13/10/2006 1,500.00 20 CBA
8469 14/10/2006 4,630.00 20 BAC
------------------------------------------------------

INPUT SHEET
???? 15/10/2006 4,630.00 20 BAC
???? 15/10/2006 4,630.00 50 BAC
???? 15/10/2006 4,630.00 100 BAC



Note that the T/A numbers keep the sequence of their OWN DEPARTMENT.

MAX is only to look for the T/A numbers of the records that have ITS OWN DEPARTMENT, Find the MAX & add 1.

 

[Fenrirshowl]

How about this:

Adjust the ranges as necessary and enter the following as an array formula (enter with shift+ctrl+return):

=LARGE((DATASHEET!$D$2:$D$20=D2)*DATASHEET!$A$2:$A$20,1)+1

Fen

 

[MarkBeck]

Will try

 

[firefytr]

Fen's formula will work, but I would use different referencing. Plus it appears that (in my sample) it would be column E and not D (depending on how your data was setup)...

=LARGE((DATASHEET!$E$2:$E13=$E14)*DATASHEET!$A$2:$A13,1)+1

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci