Dashboard table

[3G]

Hello-

I am building a dashboard with various metrics, and, am interested in creating a list within the dashboard. I would like to be able to present a list of "projects starting this current month". I have a list of 120 projects, all of which have a start date of the first of the month from 2011-2013. On the dashboard, I have a heading that says "Projects Starting this Month" with the "Today()" formula right after in the adjacent cell.


What I'd like to do is present the list of projects on the different worksheet, look at column F (Start date), and, display the project name (Column B), based on the result of the TODAY() formula. So...essentially the dashboard is current upon opening. I've tried a few IF formulas, but, it only brings back the first value


Thanks

3G

 

[jhargram]

Can you please update me the sheet.

 

[vasim]

Ok it seems like I have some solution; I beleive there could be an easier way, but this striked first in my mind.


Instead of today formula type:

=DATE(YEAR(TODAY()),MONTH(TODAY()),1)

This will give you current month's first date.


Now next to the cell/column you have the projects in, give the following formula

=if(A1=$D$1,B1,"")

where

A1 = the project start date available against projects

$D$1 = =DATE(YEAR(TODAY()),MONTH(TODAY()),1)

B1 = Name of your project

to simplifiy further, if the formula date matches the project date then show the project name else blank. Type this formula in front of all the projects that you have (120 projects).


For cells that you have entered the above formula enter range name as "blanksrange" for the cells where you want results define the range as "noblanksrange".(120 rows)


In the nonblanksrange,(120 rows) type/drag the following array formula.


=IF(ROW()-ROW(noblanksrange)+1>ROWS(blanksrange)-COUNTBLANK(blanksrange),"",INDIRECT(ADDRESS(SMALL((IF(blanksrange<>"",ROW(blanksrange),ROW()+ROWS(blanksrange))),ROW()-ROW(noblanksrange)+1),COLUMN(blanksrange),4)))

Remember its an array formula, hence to be entered as Ctrl+shift & enter.

 

[Luke M]

Branching off of Indian's idea...


Start of month formula (in cell A2):

=EOMONTH(TODAY(),-1)+1 'This requires the Analysis ToolPak add-in


Also need an end of month formula (in cell B2):

=EOMONTH(TODAY(),0)


I'll assume the list of projects is on sheet "My Data"

Array formula:

=IF(COUNTIF('My Data'!F:F,">="&$A$2)-COUNTIF('My Data'!F:F,">"&$B$2)<ROWS(A$1:A1),"",INDEX('My Data'!B:B,SMALL(IF(('My Data'!F$2:F$200>=$A$2)*('My Data'!F$2:F$200<=$B$2),ROW(F$2:F$200)),ROW(A1))))


Change the F2:F200 array callouts as needed. Copy down as far as would ever be needed.


Similar problem with formula explained:

http://chandoo.org/forums/topic/set-up-a-dynamic-list