PDA

View Full Version : problem with date formula



arnab0711
03-27-2012, 12:58 AM
Hi,
I have this final sheet where there is a formula from N4-O8,my query is regarding the date part at the end of the formula.
The date formula changes at each change in dropdown tab in graph sheet.
each time the date changes the cell N1 of final sheet also changes.
My problem - At the current stage in graph sheet Aug-11 is selected and subsequently somefigures are updated in cells N4-o8 of final sheet but wheen you go to the source you will see non of the datas pertain to Aug-11
all are from Jan -12,but still figures are showing,how is that possible.

Bob Phillips
03-27-2012, 01:29 AM
Change your formula to


=((IF(ISNUMBER($B4),IF(INDEX(Graph!$E$8:$E$12,$B4),
SUMPRODUCT(SUMIFS(INDIRECT($M4&"!"&$N$2),INDIRECT($M4&"!"&$M$2),INDEX($Q$3:$Q$7,$O$1),INDIRECT($M4&"!"&$M$3),INDEX(months,$N$1-1))),NA()),NA()))/100000)*10

arnab0711
03-27-2012, 02:24 AM
Hi xld,
The formula is working from Jan'12 onwards,but even if there are no data for Aug'11,if I select aug'11 its showing data,please advice how it is possible.

Bob Phillips
03-27-2012, 02:31 AM
I think I used the wrong list of dates, so try


=((IF(ISNUMBER($B4),IF(INDEX(Graph!$E$8:$E$12,$B4),
SUMPRODUCT(SUMIFS(INDIRECT($M4&"!"&$N$2),INDIRECT($M4&"!"&$M$2),INDEX($Q$3:$Q$7,$O$1),INDIRECT($M4&"!"&$M$3),INDEX(Graph!$AM$1:$AM$12,$N$1))),NA()),NA()))/100000)*10