Array to find rows need to insert date criteria
In case you didn't notice, I left off two 0's in the Column C range.
Should be:
(TEXT(Data!$C$2:$C$3000,"mmyy")=TEXT($J$4,"mmyy"))
--
Regards,
RD
---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Ragdyer" wrote in message
...
BTW,
I didn't test for the year, so the formula will return *all* rows that
contain the month of August for *any* year.
To include the year in the reference, just revise the Text() functions to:
(TEXT(Data!$C$2:$C$30,"mmyy")=TEXT($J$4,"mmyy"))
--
HTH,
RD
--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit !
--------------------------------------------------------------------------
-
"Ragdyer" wrote in message
...
I'm assuming that Column C is in the Data sheet, and the *array* formula
and
J4 are *not*:
=SMALL(IF((Data!$AE$2:$AE$30000)*(TEXT(Data!$C$2: $C$3000,"mmm")=TEXT($J$4,"
mmm")),ROW(Data!$AE$2:$AE$3000)),ROWS($1:1))
--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter,
instead
of
the regular <Enter, which will *automatically* enclose the formula in
curly
brackets, which *cannot* be done manually.
You *must also* use CSE when revising the formula.
*After* the CSE entry, copy down as needed.
--
HTH,
RD
--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!
--------------------------------------------------------------------------
-
"Excel 2003 - SPB" wrote in
message
...
How do I include a date limitation for a selected month into this
array,
the
date column is €śC€ť the date is in J4 enters as 8/1/07
This returns the correct row numbers
=SMALL(IF(Data!$AE$2:Data!$AE$30000,ROW(Data!$AE$ 2:Data!$AE$3000)),ROW(1:1)
)
=SMALL(IF(Data!$AE$2:Data!$AE$30000,ROW(Data!$AE$ 2:Data!$AE$3000)*MONTH(Dat
a!$C$2:Data!$C$3000=MONTH($J$4))),ROW(1:1))
Thanks SPB
|