Chart In Excel

Hi Experts,

I have one file which working  perfectly. I need little change in that - in WB there is two sheets Data & Chart. in chart sheet it shows chart  of sheet Data. there is 2 more lines which represent moving average of two different time frame ....I need 2 buttons in chart sheet it self which add up or minus values 1 from cell G1 and those lines accordingly change in chart...just like  line scrolls buttons which is there in existing chart sheet. and if possible in chart window theses names changes accordingly as in current situation I see Line 10 and Line 20 in chart window so if we change values via button these names accordingly change ...say we set to 15 and 30 via buttons then Line 15 and Line30.

See attached

Naresh PatelFinancial AdviserAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:

as I understand it, the number in G1 and H1 is used in the formula and you want to use a series label that shows what number is used.

In the attached file I have created a helper table in the Data sheet where you can enter text that you want to display before and after the number in G1 and H1 respectively. I have put in aaa, bbb, ccc, ddd.  Please replace with your desired text. You can leave cells empty, too.

The two highlighted cells have the final text that will be used as the series name. Change the formula to suit your needs.

The two highlighted cells have been assigned the range names "BlueLine" and "RedLine" accordingly.

You can now change the numbers in G1 or H1. The numbers will be used by your formula, and the numbers will also appear in the highlighted cells.

On the Chart sheet, I have changed the series definition for the two lines, so that the series name now refers to the range names on the Data sheet. Inspect the data source for the two lines to see the formula.

Let me know how you get on with this.

cheers, teylyn
Naresh PatelFinancial AdviserAuthor Commented:
Yes it good but need two buttons in chart window which increase or decrease value of  cell G1 and H1....just like other two buttons in chart window say if I clicked on button for Cell G1 in chart for increase side it will increase value of cell G1 and if I clicked on decrease side it will decrease value of cell G1. same for cell H1 .....and minimum number must in both cell is 1 below that it cant go.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Ok, I see what you mean.

I've added two spin controls on the chart sheet.  These controls will set the numbers in cells G1 and H1 on the Data sheet. I've set the controls to minimum = 0 and maximum = 100. Right click the control and click "Properties" to change the setting.

This is the beauty of Excel. You can add a control tool to adjust a number in a cell and your chart will update. Isn't it great how things all slot in and work together?

I love this kind of thing. Don't you?

cheers, teylyn

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Naresh PatelFinancial AdviserAuthor Commented:
Of course yes Teylyn mam. Even I love this about Excel. Things just become so simple with a little modification. And thanx for helping me out :) . Hope we continue such interactions in future as well.

Thank You Very Much
Naresh PatelFinancial AdviserAuthor Commented:
Sorry for bother you and I said this will be last question but sudden one thing comes in mind as if both lines appear same time is distract to draw for its purpose so need to invisible one when adjusting for other and vice versa via check box.    possible ?   regarding that I had asked new question. and here is the link. if you have spare time then please look in to this.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.