If you want to add target line in your chart and want to make it dynamic, this article will guide you step by step, how to add target line with check box.
You will also learn how to create dynamic chart title, so that you don’t have to settle for static chart titles. When the data changes the title changes.
Click Here to download the Sample workbook of Dynamic Line Chart
Add Check box Control:
Click on Develop Tab > Under Controls Panel > Click on Insert > Select Checkbox from Form Control List.
Once you create the checkbox, you need to link a cell for its status.
Select Checkbox > Right Click > Select Format Control > In Cell Link (reference box), Insert the any cell address, you want to link with checkbox.
This cell will display the status of Check box as TRUE or FALSE.
Now with the IF function for Target Values, you can create a new reference table.
Add Dynamic Chart Title:
Use Countif Function like this =COUNTIF($C$12:$C$16,”<400”) to calculate total number of Salesman have not meet the target. Then Concatenate it with Satement like this,
=COUNTIF($C$12:$C$16,”<400″)&” of the 5 Salesman did not meet the Target this Month”
Now add the Textbox in the Chart Title, To Add it Click on Insert Tab > Under Illustration Panel > Click on Shapes and Select Text Box
Now Select Textbox and insert Concatenate Statement cell address in the formula bar.