| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Array Formula for date
I am trying to display all the dates for a particular month from a list. The
data is accumulated on the Log sheet and is pulled to the Monthly Log sheet based on a dropdown. =INDEX(Log!$A$1:$O$2000,SMALL(IF((Log!$B$1:$B$2000 <($Q$1), Log!$b$1:$b$2000($Q$2)),ROW(Log!$A$1:$O$2000)),RO W(Log!2:2)),1) where q1 is beginning of month q2 is end of month How do I set this formula to just pull dates from the month displayed in the dropdown. (I hope this makes sense.) |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Array Formula for date
What is the format of the drop down selection?
Are they the month names like September? What about the dates in Log!$B$1:$B$2000, are they all the same year? Do you want the values returned based solely on the month or month of a specific year? Also, your formula is returning values from column A but you're testing column B for the date. Biff "caj" wrote in message ... I am trying to display all the dates for a particular month from a list. The data is accumulated on the Log sheet and is pulled to the Monthly Log sheet based on a dropdown. =INDEX(Log!$A$1:$O$2000,SMALL(IF((Log!$B$1:$B$2000 <($Q$1), Log!$b$1:$b$2000($Q$2)),ROW(Log!$A$1:$O$2000)),RO W(Log!2:2)),1) where q1 is beginning of month q2 is end of month How do I set this formula to just pull dates from the month displayed in the dropdown. (I hope this makes sense.) |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Array Formula for date
The dropdown has the months which brings up the first day of the month (q1)
and the last day (q2). Yes I am testing for b and returning vaules from column a. I want to pull in the same information from the Log sheet to the monthly Log sheet, in the same format, but only for the month speciifed in the dropdown. "Biff" wrote: What is the format of the drop down selection? Are they the month names like September? What about the dates in Log!$B$1:$B$2000, are they all the same year? Do you want the values returned based solely on the month or month of a specific year? Also, your formula is returning values from column A but you're testing column B for the date. Biff "caj" wrote in message ... I am trying to display all the dates for a particular month from a list. The data is accumulated on the Log sheet and is pulled to the Monthly Log sheet based on a dropdown. =INDEX(Log!$A$1:$O$2000,SMALL(IF((Log!$B$1:$B$2000 <($Q$1), Log!$b$1:$b$2000($Q$2)),ROW(Log!$A$1:$O$2000)),RO W(Log!2:2)),1) where q1 is beginning of month q2 is end of month How do I set this formula to just pull dates from the month displayed in the dropdown. (I hope this makes sense.) |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Array Formula for date
Try this:
=INDEX(Log!$A$1:$A$2000,SMALL(IF((Log!$B$1:$B$2000 =$Q$1)* (Log!$B$1:$B$2000<=$Q$2),ROW(Log!A$1:A$2000)),ROWS ($1:1))) Array entered Biff "caj" wrote in message ... The dropdown has the months which brings up the first day of the month (q1) and the last day (q2). Yes I am testing for b and returning vaules from column a. I want to pull in the same information from the Log sheet to the monthly Log sheet, in the same format, but only for the month speciifed in the dropdown. "Biff" wrote: What is the format of the drop down selection? Are they the month names like September? What about the dates in Log!$B$1:$B$2000, are they all the same year? Do you want the values returned based solely on the month or month of a specific year? Also, your formula is returning values from column A but you're testing column B for the date. Biff "caj" wrote in message ... I am trying to display all the dates for a particular month from a list. The data is accumulated on the Log sheet and is pulled to the Monthly Log sheet based on a dropdown. =INDEX(Log!$A$1:$O$2000,SMALL(IF((Log!$B$1:$B$2000 <($Q$1), Log!$b$1:$b$2000($Q$2)),ROW(Log!$A$1:$O$2000)),RO W(Log!2:2)),1) where q1 is beginning of month q2 is end of month How do I set this formula to just pull dates from the month displayed in the dropdown. (I hope this makes sense.) |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Array Formula for date
Thank You. That worked great.
"Biff" wrote: Try this: =INDEX(Log!$A$1:$A$2000,SMALL(IF((Log!$B$1:$B$2000 =$Q$1)* (Log!$B$1:$B$2000<=$Q$2),ROW(Log!A$1:A$2000)),ROWS ($1:1))) Array entered Biff "caj" wrote in message ... The dropdown has the months which brings up the first day of the month (q1) and the last day (q2). Yes I am testing for b and returning vaules from column a. I want to pull in the same information from the Log sheet to the monthly Log sheet, in the same format, but only for the month speciifed in the dropdown. "Biff" wrote: What is the format of the drop down selection? Are they the month names like September? What about the dates in Log!$B$1:$B$2000, are they all the same year? Do you want the values returned based solely on the month or month of a specific year? Also, your formula is returning values from column A but you're testing column B for the date. Biff "caj" wrote in message ... I am trying to display all the dates for a particular month from a list. The data is accumulated on the Log sheet and is pulled to the Monthly Log sheet based on a dropdown. =INDEX(Log!$A$1:$O$2000,SMALL(IF((Log!$B$1:$B$2000 <($Q$1), Log!$b$1:$b$2000($Q$2)),ROW(Log!$A$1:$O$2000)),RO W(Log!2:2)),1) where q1 is beginning of month q2 is end of month How do I set this formula to just pull dates from the month displayed in the dropdown. (I hope this makes sense.) |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Array Formula for date
You're welcome. Thanks for the feedback!
Biff "caj" wrote in message ... Thank You. That worked great. "Biff" wrote: Try this: =INDEX(Log!$A$1:$A$2000,SMALL(IF((Log!$B$1:$B$2000 =$Q$1)* (Log!$B$1:$B$2000<=$Q$2),ROW(Log!A$1:A$2000)),ROWS ($1:1))) Array entered Biff "caj" wrote in message ... The dropdown has the months which brings up the first day of the month (q1) and the last day (q2). Yes I am testing for b and returning vaules from column a. I want to pull in the same information from the Log sheet to the monthly Log sheet, in the same format, but only for the month speciifed in the dropdown. "Biff" wrote: What is the format of the drop down selection? Are they the month names like September? What about the dates in Log!$B$1:$B$2000, are they all the same year? Do you want the values returned based solely on the month or month of a specific year? Also, your formula is returning values from column A but you're testing column B for the date. Biff "caj" wrote in message ... I am trying to display all the dates for a particular month from a list. The data is accumulated on the Log sheet and is pulled to the Monthly Log sheet based on a dropdown. =INDEX(Log!$A$1:$O$2000,SMALL(IF((Log!$B$1:$B$2000 <($Q$1), Log!$b$1:$b$2000($Q$2)),ROW(Log!$A$1:$O$2000)),RO W(Log!2:2)),1) where q1 is beginning of month q2 is end of month How do I set this formula to just pull dates from the month displayed in the dropdown. (I hope this makes sense.) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Array Formula Not Working with Range with Formulas | Excel Discussion (Misc queries) | |||
| Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
| Array formula returning wrong results | Excel Discussion (Misc queries) | |||
| Changing a Formula in Conditional Formatting | Excel Worksheet Functions | |||
| Conditional formula - language needed | Excel Worksheet Functions | |||
Hybrid Mode
