| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Array to find rows need to insert date criteria
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(Data!$C$2:Data!$C$3000=MONT H($J$4))),ROW(1:1)) Thanks SPB |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Array to find rows need to insert date criteria
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Array to find rows need to insert date criteria
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Array to find rows need to insert date criteria
Thanks
spent many hours, never thought of a "text string" "Ragdyer" wrote: 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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Array to find rows need to insert date criteria
You're welcome, and appreciate your feed-back.
-- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Excel 2003 - SPB" wrote in message ... Thanks spent many hours, never thought of a "text string" "Ragdyer" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| find date that meets a criteria | Excel Discussion (Misc queries) | |||
| find date that meets a criteria | Excel Discussion (Misc queries) | |||
| In an array, I need to find the row # that meets 2 criteria | Excel Worksheet Functions | |||
| Can I use "find / replace" to automatically "Insert Rows" in a do. | Excel Discussion (Misc queries) | |||
| Insert Rows into an existing array | New Users to Excel | |||
Linear Mode
