Formula Help....

[markster]

Hi guys.

Here is an extract of a spreadsheet (Sheet 2) that I'm using that lists all payments made by project. I have grouped all payments for 1 project on this example for illustrative purposes, but in reality they are contained separately in a 10,000 lined spreadsheet.

Excel Workbook
	A	B	C	D	E
1	Reference Number	Project Name	PO Number	Invoice Date/Claim Date	Invoice Amount
2	JA05	Sustainable Living		07/02/2006	15,650.65
3	JA05	Sustainable Living		25/10/2006	18,444.51
4	JA05	Sustainable Living		11/12/2007	52,739.65
5	JA05	Sustainable Living		15/03/2007	34,877.56
6	JA05	Sustainable Living		10/07/2007	33,916.12
7	JA05	Sustainable Living		14/04/2008	33,267.77
8	JA05	Sustainable Living		15/05/2008	16,775.53
9	JA05	Sustainable Living		04/08/2008	3,565.77
Sheet1

Excel 2003

I have a master spreadsheet in Sheet 1, Ref Number in column A of this spreadsheet, I just need a look up formula that will look up to the reference number in (Sheet 2 column A) and then just return the date of the last payment made (from column D). In the example, above the date returned would be 4/8/2008. Can anyone help?

PLEASE NOTE THE EXTRACT ABOVE SHOWS SHEET 1 - IN REALITY THIS INFORMATION IS CONTAINED IN SHEET 2 - APOLOGIES FOR THE CONFUSION

Cheers

Mark

 

[Haseeb Avarakkan]

Hello,

Try this in Master Sheet.

=MAX(IF(Sheet2!$A$2:$A$1000=$A2,Sheet2!$D$2:$D$1000))

Confirmed with Control+Shift+Enter, not just enter

Where $A2 is your Ref Number.

Also Pivot Table is more faster than formula. Try the excel help & Google.

 

[alansidman]

Excel Workbook
	B	C	D	E	F	G	H
1	Project Name	PO Number	Invoice Date/Claim Date	Invoice Amount		Criteria	JA05
2	Sustainable Living		02/07/06	15,650.65		08/04/08
3	Sustainable Living		10/25/06	18,444.51
4	Sustainable Living		12/11/07	52,739.65
5	Sustainable Living		03/15/07	34,877.56
6	Sustainable Living		07/10/07	33,916.12
7	Sustainable Living		04/14/08	33,267.77
8	Sustainable Living		05/15/08	16,775.53
9	Sustainable Living		08/04/08	3,565.77
Sheet1

 

[markster]

Thanks for your helpg guys. Have a good day.

M