• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need to Create a Dynamic Chart Based On Daily Report and Monthly Report

X

xcruc1at3r

Member
I need help for creating a Impressive Dynamic Chart Report Like Chandoo's

http://chandoo.org/wp/2010/08/31/dynamic-chart-with-check-boxes/ << like this one

and i am using excel 2003

daily Has two options And Monthly as well Com and NonCom and each Of these two and Additional 2 options Tie & Untie


Do hit BACK :)
 
There shouldn't be anything about that chart that wouldn't work in excel 03.


Which part are you having trouble with?
 
The basic idea of Chandoo's chart remains the same. Use either check boxes or toggle buttons to influece some cells calculations. Those changing calculations will be whats plotted on your graph.


Chandoo has a nice starter library of articles on dynamic charts:

http://chandoo.org/wp/tag/dynamic-charts


Jon Peltier also has several which might help:

http://peltiertech.com/WordPress/dynamic-charts/
 
bingo! addins made it appear the data....


when i click only Com-Total & UnCom T0tal the report becomes #N/A and the year chart shows nothing
 
Whoops


Change

Summery-Report!D23 to:


=IF($B$23=TRUE,SUMPRODUCT((Date>=D$20)*(Date<=EOMONTH(D$20,0)*(Remarks=$B$10)))+SUMPRODUCT((Date>=D$20)*(Date<=EOMONTH(D$20,0)*(Remarks=$B$11))),NA())


&


Summery-Report!D28 to:

=IF($B$28=TRUE,SUMPRODUCT((Date>=D$20)*(Date<=EOMONTH(D$20,0)*(Remarks=$B$14)))+SUMPRODUCT((Date>=D$20)*(Date<=EOMONTH(D$20,0)*(Remarks=$B$15))),NA())


Copy both formula across


The #N/A errors are by design so that the series don't show up on the chart at all
 
Summery-Report!D23 is : =IF(B23=TRUE,D21+D22,0) changed it Into


=IF($B$23=TRUE,SUMPRODUCT((Date>=D$20)*(Date<=EOMONTH(D$20,0)*(Remarks=$B$10)))+SUMPRODUCT((Date>=D$20)*(Date<=EOMONTH(D$20,0)*(Remarks=$B$11))),NA())


Summery-Report!D28 also has been replaced with

=IF($B$28=TRUE,SUMPRODUCT((Date>=D$20)*(Date<=EOMONTH(D$20,0)*(Remarks=$B$14)))+SUMPRODUCT((Date>=D$20)*(Date<=EOMONTH(D$20,0)*(Remarks=$B$15))),NA())


in B14 is usm (remark) and if 1 More Remark in B15 for e.g (ABC) like COM & Mnr so i just have to replace in D27

from:

=IF($B27=TRUE,SUMPRODUCT((Date>=D$20)*(Date<=EOMONTH(D$20,0)*(Remarks=$B$14))),NA())

To:

=IF($B27=TRUE,SUMPRODUCT((Date>=D$20)*(Date<=EOMONTH(D$20,0)*(Remarks=$B$15))),NA())

and Copy both formula across right !!


Okies
 
HUI:


in B14 is usm (remark) and if 1 More Remark in B15 for e.g (ABC) like COM & Mnr so i just have to replace in D27

from:

=IF($B27=TRUE,SUMPRODUCT((Date>=D$20)*(Date<=EOMONTH(D$20,0)*(Remarks=$B$14))),NA())

To:

=IF($B27=TRUE,SUMPRODUCT((Date>=D$20)*(Date<=EOMONTH(D$20,0)*(Remarks=$B$15))),NA())

and Copy both formula across right !!
 
To both posts


Yes. Your old formulas both usm and so I left it as was, knowing that it was wrong
 
U r really a sensible person...well because it was a test report and the Remark was not decided up till now due to what i did not place any for the UNCOM untie now that its clear so i have to place the Remark for untie so we can get it going...


Thanks a Million Friend....For Allllllllllllllllllllllllllllllll The Help and Support..

God Bless U and your Dear Family...
 
i am changing =SUMPRODUCT((Data!$G$1:$G$100=DATE($B$5,myMonth,D$9))*(Data!$I$1:$I$100="usm")) to >> "untie" but its showing #NA error...

and when i change

=SUMPRODUCT((Date=DATE($B$5,myMonth,D$9))*(Remarks=$B$14)) >>>to $B$15 it shows the same #NA error...

because i have to add remarks for UNCOM-UNTIE as "Untie"
 
Change D15: to

=SUMPRODUCT((Date=DATE($B$5,myMonth,D$9))*(Remarks=$B$15))

Copy across


I put "untie" in Data!I24 without the " 's

it works as expected


Make sure that Untie doesn't have a Leading or Trailing space
 
hui it started working but another thing! we are trying to maintain a database which should like to 4000+ columns right but i guess the formula's are limited till till data!column 89 right !!!dear...
 
The techniques used here use two Named Formula, Date and Remarks which are dynamic in that they expand as you add more records to the Data table.


Have a look at the Named Formula in the Name Manager for more details.


The other fields are also available as named formula if needed.
 
i did as directed

the Chart is Showing #N/A after replacing the formula and adding the remark in B15

should i send u the file !
 
Works like a Charm Mr. Excel Ninja Specialist :)

well in match formula...generally asking

=MATCH('Summery-Report'!$B$4,tblMonth,0) if 0 changed to 1 what does it means ?
 
You must log in or register to reply here.
Back
Top