I am trying to return the values of column Ain column C if the dates in
column B are between 31/12/2008 and 01/01/2010

Column A Column B Column C
1040 24/12/2008 1042
1042 03/01/2009 1041
1041 21/06/2009 1043
1043 21/12/2009
1045 03/01/2010

Have tried =IF(AND(B:B31/12/2008,B:B<01/01/2010),A:A,0)

In cell A1 and copy down as required
=IF(AND(B1DATE(2008,12,31),B1<DATE(2010,1,1)),A1, 0)

If this post helps click Yes
---------------
Jacob Skaria


"Rob" wrote:

I am trying to return the values of column Ain column C if the dates in
column B are between 31/12/2008 and 01/01/2010

Column A Column B Column C
1040 24/12/2008 1042
1042 03/01/2009 1041
1041 21/06/2009 1043
1043 21/12/2009
1045 03/01/2010

Have tried =IF(AND(B:B31/12/2008,B:B<01/01/2010),A:A,0)

This has worked fine, but is their any way i can just have the values where
this argument is applicable instead of 0 when it doesn't

"Jacob Skaria" wrote:

In cell A1 and copy down as required
=IF(AND(B1DATE(2008,12,31),B1<DATE(2010,1,1)),A1, 0)

If this post helps click Yes
---------------
Jacob Skaria


"Rob" wrote:

I am trying to return the values of column Ain column C if the dates in
column B are between 31/12/2008 and 01/01/2010

Column A Column B Column C
1040 24/12/2008 1042
1042 03/01/2009 1041
1041 21/06/2009 1043
1043 21/12/2009
1045 03/01/2010

Have tried =IF(AND(B:B31/12/2008,B:B<01/01/2010),A:A,0)

Hi,
use

=IF(AND(B1DATE(2008,12,31),B1<DATE(2010,1,1)),A1, "")


"Rob" wrote:

This has worked fine, but is their any way i can just have the values where
this argument is applicable instead of 0 when it doesn't

"Jacob Skaria" wrote:

In cell A1 and copy down as required
=IF(AND(B1DATE(2008,12,31),B1<DATE(2010,1,1)),A1, 0)

If this post helps click Yes
---------------
Jacob Skaria


"Rob" wrote:

I am trying to return the values of column Ain column C if the dates in
column B are between 31/12/2008 and 01/01/2010

Column A Column B Column C
1040 24/12/2008 1042
1042 03/01/2009 1041
1041 21/06/2009 1043
1043 21/12/2009
1045 03/01/2010

Have tried =IF(AND(B:B31/12/2008,B:B<01/01/2010),A:A,0)

Thanks Eduardo, but i reraly need to see just the value not a blank. In the
example the first value in Column C is 1042 i.e the first value that meets
the criteria and excludes 1040 which is outside this range. Is their any way
to automatically sort the data ?

"Eduardo" wrote:

Hi,
use

=IF(AND(B1DATE(2008,12,31),B1<DATE(2010,1,1)),A1, "")


"Rob" wrote:

This has worked fine, but is their any way i can just have the values where
this argument is applicable instead of 0 when it doesn't

"Jacob Skaria" wrote:

In cell A1 and copy down as required
=IF(AND(B1DATE(2008,12,31),B1<DATE(2010,1,1)),A1, 0)

If this post helps click Yes
---------------
Jacob Skaria


"Rob" wrote:

I am trying to return the values of column Ain column C if the dates in
column B are between 31/12/2008 and 01/01/2010

Column A Column B Column C
1040 24/12/2008 1042
1042 03/01/2009 1041
1041 21/06/2009 1043
1043 21/12/2009
1045 03/01/2010

Have tried =IF(AND(B:B31/12/2008,B:B<01/01/2010),A:A,0)

An easy formulas play which can deliver it for you ..
Source data as posted running in A1:B1 down
In C1: =IF(AND(B1DATE(2008,12,31),B1<DATE(2010,1,1)),ROW (),"")
In D1: =IF(ROW()COUNT(C:C),"",INDEX(A:A,SMALL(C:C,ROW()) ))
Copy C1:D1 down to cover the max expected extent of source data.
Hide/minimize col C. Col D will return the required results, all neatly
bunched at the top
Any good? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Rob" wrote:
.. In the example the first value in Column C is 1042
i.e the first value that meets the criteria and excludes 1040
which is outside this range. Is there any way
to automatically sort the data ?

Thank you all for your answers they have all worked fine. One more questiuon
though using the formula that Max wrote, how can I change it to read row 2. I
have inserted a header in row 1 and therefore miss the first line of data
when i use the formula in D2 instead of D1

"Max" wrote:

An easy formulas play which can deliver it for you ..
Source data as posted running in A1:B1 down
In C1: =IF(AND(B1DATE(2008,12,31),B1<DATE(2010,1,1)),ROW (),"")
In D1: =IF(ROW()COUNT(C:C),"",INDEX(A:A,SMALL(C:C,ROW()) ))
Copy C1:D1 down to cover the max expected extent of source data.
Hide/minimize col C. Col D will return the required results, all neatly
bunched at the top
Any good? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Rob" wrote:
.. In the example the first value in Column C is 1042
i.e the first value that meets the criteria and excludes 1040
which is outside this range. Is there any way
to automatically sort the data ?

Just replace ROW() with ROWS($1:1),
viz use in D2, copied down:
=IF(ROWS($1:1)COUNT(C:C),"",INDEX(A:A,SMALL(C:C,R OWS($1:1))))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Rob" wrote:
Thank you all for your answers they have all worked fine. One more questiuon
though using the formula that Max wrote, how can I change it to read row 2. I
have inserted a header in row 1 and therefore miss the first line of data
when i use the formula in D2 instead of D1

Working fine now. thank you all.

"Max" wrote:

Just replace ROW() with ROWS($1:1),
viz use in D2, copied down:
=IF(ROWS($1:1)COUNT(C:C),"",INDEX(A:A,SMALL(C:C,R OWS($1:1))))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Rob" wrote:
Thank you all for your answers they have all worked fine. One more questiuon
though using the formula that Max wrote, how can I change it to read row 2. I
have inserted a header in row 1 and therefore miss the first line of data
when i use the formula in D2 instead of D1