DataBase worksheet function

[Jean-Yves]

Hello,

I would like to find the equivalent XL formula for this situation:

Payrol - Name - Startdate - EndDate - Function
123 Bob 01/01/2008 31/12/2008 Coach
124 Greg 01/01/2007 31/12/2007 Coach
124 Greg 01/01/2007 31/12/2007 Coach
125 Henry 01/01/2007 31/12/2007 Coach
125 Henry 01/01/2008 31/12/2008 Supervisor

The lookup should be one in the same workbook.

SELECT Max(EndDate) FROM [Workseets(1)!DataRange] WHERE Payroll = '125'

It should return "31/12/2008" for Henry (payroll:125)

Or eventually, can I use SQL to query this workbook directly. I know how to
do it in ADO to a closed workbook,
but i never used it for a kind of lookup in the actual workbook.

Thank you in advance

Jean-Yves Tfelt

 

[Jim Cone]

With help from Bob Phillips...
If your data is in B20:F25 then
=MAX(IF(B21:B25=125,E21:E25)) ... entered as an array formula.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Jean-Yves"
wrote in message
Hello,
I would like to find the equivalent XL formula for this situation:

Payrol - Name - Startdate - EndDate - Function
123 Bob 01/01/2008 31/12/2008 Coach
124 Greg 01/01/2007 31/12/2007 Coach
124 Greg 01/01/2007 31/12/2007 Coach
125 Henry 01/01/2007 31/12/2007 Coach
125 Henry 01/01/2008 31/12/2008 Supervisor

The lookup should be one in the same workbook.
SELECT Max(EndDate) FROM [Workseets(1)!DataRange] WHERE Payroll = '125'
It should return "31/12/2008" for Henry (payroll:125)
Or eventually, can I use SQL to query this workbook directly. I know how to
do it in ADO to a closed workbook,
but i never used it for a kind of lookup in the actual workbook.
Thank you in advance
Jean-Yves Tfelt

 

[Bob Phillips]

=MAX(IF(A2:A20=125,D220))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

Note that you cannot use a whole column in array formulae (prior to excel
2007), but must use an explicit range.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Jean-Yves]

Hello Jim,

I will giive a go asap and keep you informed
Thank you
Regards
JY

 

[Bob Phillips]

What? Before I had even posted <g>

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Jim Cone]

Bob,
You have to keep checking your back, they might be gaining on you <g>
(you have other earlier posts)
Regards,
Jim Cone



"Bob Phillips"
wrote in message
What? Before I had even posted <g>
---
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Jim Cone"
wrote in message