• 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.

Dynamic chart based on value in another cell

Rajesh S

Member
Dear Team,

I am trying to prepare a dynamic chart based on the attached excel file.

Line Chart is required based on the value available in Column E, G & I (i.e., % of Late Comers, Stretch%, Working Hours non adherance%).

Data in Column E, G & I will be added on daily basis based on another input. While preparing a chart, output is having all the dates with blank data reading at bottom of the chart on those days.

I would like to have a dynamic chart where the chart should be prepared only till the date of data available & once the data is punched for the next day chart should get auto refreshed till such day.

Can anyone help me on this?

Regards

S Rajesh
 

Attachments

  • Chart Sample.xlsx
    10.1 KB · Views: 7
Dear Somendra,

Thanks for your prompt reply. Chart appearance is correct. But if I enter the details for next date, then chart should automatically pick up the details & change till that date.

Example: If details are updated in Row 14 (Dated: 11/09/14) then chart should automatically pick up till that date.

How it is clear now. Please help

Regards

S Rajesh
 
Dear Somendra,

Tried the same & the data is not getting auto refreshed. See the attached screenshot.

Regards

S Rajesh
 
Hi Rajesh

If you put data 22% in I14 then work backwards from there you see the effect immediately rather than working from left to right. I think what Somendra has designed works. Give it another try.

Ps there is no screen shot.

Take care

Smallman
 
Thanks Somendra & Smallman,

I am not sure what went wrong at my end earlier. It did not work. It's working fine now. Thanks for your prompt response.

It would be helpful if you share the methodology being followed which will help to boost my excel knowledge.

Thanks once again.

Regards

S Rajesh
 
@Rajesh S

Great it worked for you and thanks for the feedback.

Actually, there are three series which get data dynamically through OFFSET formula defined in Name manager.
You can access it through formula tab or Keyboard shortcut Ctrl+F3.

I also developed the dynamic range for x-axis also. Basically these formulas will get the data say in E column till the last fill data from say E5.

Go through them, and write back if you face any difficulty in understanding them.

Regards,
 
I didn't do anything Somendra deserves all your thanks. I had to look at the X series formula to see what the trigger was. Once I knew that then I knew what trigger to pull. Well done Somendra. Good post!!!
 
Hi , Please find attached excel for you reference.
 

Attachments

  • Chart Sample.xlsx
    20.7 KB · Views: 4
Hi Syedali,

Well you posted the problem in somebody else's thread (I hope you had the same problem), but you did not explained anything about the problem. That's not a good etiquette to follow when you are on a virtual community like this.

I think the problem that you were facing the lines coming to down to 0 and dates appearing on X-Axis for which the data is yet not entered. If so than see the file. I had not done any formula trick, instead follow the suggestion given by Jeff above in his comment #12 to turn the data in to Excel table. That way when you update new data, it will keep changing.

If this is not your requirement, than start a new thread with a clear description about your problem.

Regards,
 

Attachments

  • Chart Sample (1).xlsx
    23.5 KB · Views: 19
I apologize for that. Any way thank you and i want know how it work when you enter a data then only it show in graph you make it table am i correct.
 
Yes this is excel table I think it's available in excel 2007+ version.

To enter new data in the table you can either press TAB by placing activating last cell of the table or just enter the data below the last row in the table, table will expand automatically.

Regards,
 
Back
Top