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

[JenniferMurphy]

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

 

[Jamie McMillan]

Hello Jenifer,

If the calculation mode is manual; you will need to press F9 to calculate it - if not it will automatically update.

I presume that the x values are in column A - And there is no data in the following columns.
I used columns S & T. S is the x value T is the y value.

In cell S5 type.
=A5 (If this is where the x value is)

In cell T5 copy & paste the following formula.
=IF($T$1=1,B5,IF($T$1=2,C5,IF($T$1=3,D5,IF($T$1=4,E5,IF($T$1=5,F5,IF($T$1=6,G5,IF($T$1=7,H5,IF($T$1=8,I5,IF($T$1=9,J5,IF($T$1=10,K5,IF($T$1=11,L5,M5)))))))))))

Select cells S5 & T5 and copy down to row 100

Now you will have the x value in Cell S5 and the y value in T5.
Set the charts data range for the data in columns S & T.
Now when you put a number 1 to 12 in cell T1 it will select the required data. (Press F9 if on manual calculation.)

***Note if there is no number in cell T1 - or any number greater than 11 it will return column 12s data.***
When editing you DO NOT change columns S & T - Edit the values in Range A5 to M100.

Let me know if this is OK for you.

Jamie McMillan

 

[StephenCrump]

Why not just use an INDEX() formula to pick up the column you want?

Book32
	A	B	C	D	E	F
1
2				Datasets
3	Dataset	1		1	2	3
4	x	y
5	1	1		1	3	7
6	2	4		4	6	9
7	3	9		9	9	11
8	4	16		16	12	13
9	5	25		25	15	15
Sheet1

Cell Formulas
Range	Formula
B5:B9	B5	=INDEX(MyData,,B3)
D5:D9	D5	=A5^2
E5:E9	E5	=3*A5
F5:F9	F5	=A5*2+5
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
MyData	=Sheet1!$D$5:$F$9	B5
MyY	=INDEX(MyData,,Sheet1!$B$3)	B5

For the graph, you can point to B5:B9 for the y values.

Or you could point to Sheet1!MyY and avoid having to display the chosen y values at all:

 

[JenniferMurphy]

Hello Jenifer,

In cell S5 type.
=A5 (If this is where the x value is)

In cell T5 copy & paste the following formula.
=IF($T$1=1,B5,IF($T$1=2,C5,IF($T$1=3,D5,IF($T$1=4,E5,IF($T$1=5,F5,IF($T$1=6,G5,IF($T$1=7,H5,IF($T$1=8,I5,IF($T$1=9,J5,IF($T$1=10,K5,IF($T$1=11,L5,M5)))))))))))

. . .

Jamie McMillan

Thanks Jamie. I will never be able to type that formula without many, many errors. And I need to add more columns of test data, so I'd have to contantly edit it. But it contains the basis of a solution.

I was going to physically move the selected column of data to the Y column. Your method of pointing the Y column to the selected test data is much better. I'm going to write a macron that will ask me for the column letter of the test data. It will then merely change the expressions in the Y columns to refer to that test column.

Thanks

 

[JenniferMurphy]

Why not just use an INDEX() formula to pick up the column you want?

That's an interesting approach. The main problem I see is that I will be adding new test data columns, so the range that MyData refers to will frequently change. Is there a way that I cam put a column letter in B3 instead of a number?

I'm going to do a little testing with that alternative.

Thanks

 

[JenniferMurphy]

How about this? The expressions in B5:B9 will acquire the contents of the cells in the same rows in the column named in B4. In this exmaple, I am getting the values in E5:E9.

Normal Distribution.xlsm
	B	C	D	E	F	G
4	e
5	5		1	5	1	5
6	4		2	4	3	3
7	3		3	3	5	1
8	2		4	2	3	3
9	1		5	1	1	5
Sheet2

Cell Formulas
Range	Formula
B5:B9	B5	=INDIRECT($B$4 & ROW())

If I change B4 to "g", I get:

Normal Distribution.xlsm
	B	C	D	E	F	G
4	g
5	5		1	5	1	5
6	3		2	4	3	3
7	1		3	3	5	1
8	3		4	2	3	3
9	5		5	1	1	5
Sheet2

Cell Formulas
Range	Formula
B5:B9	B5	=INDIRECT($B$4 & ROW())

Anything wrong with this?

 

[StephenCrump]

Anything wrong with this?

I'm sure you're aware of the general advice to avoid overuse of volatile formulae, such as INDIRECT. You could do something different, using non-volatile formulae.

But your formula works for me. Simple, and does the job. I'd be happy doing the same.

 

[JenniferMurphy]

I'm sure you're aware of the general advice to avoid overuse of volatile formulae, such as INDIRECT. You could do something different, using non-volatile formulae.

But your formula works for me. Simple, and does the job. I'd be happy doing the same.

I hear about caveats related to volatile functions, but I don't really understand the problem.

Can you post an alternative formula that does not use any volatile functions?

 

[Jamie McMillan]

Hello Jennifer,

Your original post said without macros.

If you are adding new data; then:-

Insert a new column at column A - so you have a new column A.
List the rows in column A 1, 2, 3 ... 95 (x values will now be in column B) (A5 will be row 1)

Go to a column further away from the last possible data will be entered - I used column CA

Type in CA5 --- =B5 (This will put the x value there.)

In Cell CB5 - copy and paste:- =VLOOKUP(A5,$A$5:$BZ$100,$T$1+2) Copy this formula down to cell CB100 {This will give you up to 50 y value columns 'AC-BZ'

Then set the chart's data range to CA5:BZ100

Now in cell T1 you put the number of the y data and it will all update for you. (You can change $T$1 to any empty cell)

Jamie McMillan

 

[JenniferMurphy]

Hello Jennifer,

Your original post said without macros.

My original post said that I could write a macro to do it, but was looking for a way without one. Your first post showed me a way that required no macro, no moving columns of data, no large named ranges that would need to be updated if I added new columns, and was simple.

If you are adding new data; then:-

. . .

I don't see how this is simpler or better than my solution.