Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
help with a formula
I have two work sheets. The first contains a list of clients while the second
records data on each occation we have a contact with our client. I want to extract data from the second sheet that reflects the most recent entry. It look like this: Sheet 1 Client Ref First Number Number Name Last Name 1 654 Sam Fisher 2 123 Sam Jones 3 688 Harry Smith 4 478 John Johanson Sheet 2 Date Voucher Client Centre First Last Issued Number Ref Name Name 15/08/08 1 654 Sam Fisher 15/08/09 1 654 Sam Fisher 15/08/09 3 688 Harry Smith 15/08/09 4 478 John Johanson 15/08/09 5 987 Laurey Dessmond As you can see client #1 has an entry on 15/8/08 and 15/08/09. I need a formula that can return the latest date into a cell in sheet 1. Is this possible? Please help. Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
help with a formula
One way
Assuming client num is unique, and dates in Sheet2 are real dates In Sheet1, In E2, array-entered, ie press CTRL+SHIFT+ENTER to confirm the formula: =MAX(IF(Sheet2!B$2:B$6=A2,Sheet2!A$2:A$6)) Copy down. Real dates are numbers, increasing chronologically. Hence the latest date for any particular client num would simply be its largest associated "date" number Any good? hit the YES below -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Ben" wrote: I have two work sheets. The first contains a list of clients while the second records data on each occation we have a contact with our client. I want to extract data from the second sheet that reflects the most recent entry. It look like this: Sheet 1 Client Ref First Number Number Name Last Name 1 654 Sam Fisher 2 123 Sam Jones 3 688 Harry Smith 4 478 John Johanson Sheet 2 Date Voucher Client Centre First Last Issued Number Ref Name Name 15/08/08 1 654 Sam Fisher 15/08/09 1 654 Sam Fisher 15/08/09 3 688 Harry Smith 15/08/09 4 478 John Johanson 15/08/09 5 987 Laurey Dessmond As you can see client #1 has an entry on 15/8/08 and 15/08/09. I need a formula that can return the latest date into a cell in sheet 1. Is this possible? Please help. Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
help with a formula
Put this array* formula in E3 of Sheet1:
=MAX(IF((C3=Sheet2!D$3:D$300)*(D3=E$3:E$300),Sheet 2!A$3:A$300)) I've assumed you have data in rows 3 to 300 - change as required to suit your data. Format the cell as a date, then copy down as far as needed. * An array formula must be committed using the key combination of Ctrl- Shift-Enter (CSE) instead of the usual <enter. If you do ths correctly then Excel will wrap curly braces { } around the formula when viewed in the formula bar - do not type these yourself. If you need to amend the formula you have to use CSE again. Hope this helps. Pete On Sep 11, 1:46*pm, Ben wrote: I have two work sheets. The first contains a list of clients while the second records data on each occation we have a contact with our client. I want to extract data from the second sheet that reflects the most recent entry. It look like this: Sheet 1 Client * * * *Ref * * * * * *First Number *Number *Name * *Last Name 1 * * * 654 * * Sam * * Fisher 2 * * * 123 * * Sam * * Jones 3 * * * 688 * * Harry * Smith 4 * * * 478 * * John * *Johanson Sheet 2 Date Voucher * *Client * * * *Centre * * * First * * * * Last Issued *Number *Ref * * Name * *Name 15/08/08 * * * *1 * * * 654 * * Sam * * Fisher 15/08/09 * * * *1 * * * 654 * * Sam * * Fisher 15/08/09 * * * *3 * * * 688 * * Harry * Smith 15/08/09 * * * *4 * * * 478 * * John * *Johanson 15/08/09 * * * *5 * * * 987 * * Laurey *Dessmond As you can see client #1 has an entry on 15/8/08 and 15/08/09. I need a formula that can return the latest date into a cell in sheet 1. Is this possible? Please help. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|