Is there a way to quickly select one of 10-12 Y values for a chart?

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
2,487
Office Version
  1. 365
Platform
  1. Windows
Suppose I have 10-12 sets of Y values for a chart in B5:B100, C5:C100, D5:D100, E5:E100, etc. They all have the same X values in A5:A100

Is there a way that I can quickly and easily select one of the sets of Y values and have the chart immediately display the associated graph?

I suppose I can write a macro that will let me enter the column letter (A, B, C, ...) and have it copy that set of data to the range that is assigned to the chart.

Is there an easier way?

Thanks
 
I hear about caveats related to volatile functions, but I don't really understand the problem
Hi Jennifer, the main issue with volatile functions is the formula recalculates every time the workbook recalculates, whereas a "regular" formula only calculates when it (or formulae, names or values it is dependent on) need recalculation (normally due to changes).

Obviously the more cells calculating the slower the workbook becomes and so if there are too many volatile formulae it can make the workbook very slow (as they are calculating every time there is a recalculation [even if it or what it is dependent hasn't changed]).

It is the same with too many different Conditional Format rules as Conditional Formatting is always volatile.
 
Upvote 0

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Hi Jennifer, the main issue with volatile functions is the formula recalculates every time the workbook recalculates, whereas a "regular" formula only calculates when it (or formulae, names or values it is dependent on) need recalculation (normally due to changes).

Obviously the more cells calculating the slower the workbook becomes and so if there are too many volatile formulae it can make the workbook very slow (as they are calculating every time there is a recalculation [even if it or what it is dependent hasn't changed]).

It is the same with too many different Conditional Format rules as Conditional Formatting is always volatile.
Thanks for that explanation.

Is there a good alternative to Indirect that is not volatile? I did a little checking, but couldn't find one. I just need to generate an expression like "=D7" where the "D" is in a named cell and the "7" is the row the expression is on.

Thanks
 
Upvote 0
I'm going to mark my post as the solution so that this thread has a solution. It was based on comments by both Jamie (post #2) and Stephen (post #3), so both of them deserve credit for pointing me in the right direction. Thanks to both of you.

If someone comes up with a variation of my solution that does not use any volatile functions, I'll mark that as the solution.
 
Upvote 0
If someone comes up with a variation of my solution that does not use any volatile functions ...
You've got a nice simple solution that works, i.e. where "D" indicates you want the result in column D.

A non-volatile solution would have to be structured differently.

Keep the simplicity. A little bit of volatile won't hurt.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top