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

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

=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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
>

Hello Jim,

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

"Jim Cone" <(E-Mail Removed)> wrote in message
news:e%(E-Mail Removed)...
>
> 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
>
>

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" <(E-Mail Removed)> wrote in message
news:e%(E-Mail Removed)...
>
> 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,
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
> 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)