Chart data change via selected named range(s) in validation cell...help please...

[aiki100]

Hello all - I am hoping someone may have some input on this, please:

I have created 5 named ranges from a table. Each named range is simply one column of data.

Then created a lookup for the (names of) these named ranges (names match exactly, etc.)

Once I had a lookup list, I then created a validation list in a cell, made up of the looked-up named ranges.

So: I wish to have chart data reflect the choice of the chosen named range from the validation list.

So I am either doing too much, or too little, or doing it completely wrong, as I cannot get the chart to select the named ranges via the validation list, which (would/should) then update the chart to reflect the data in that actual named range.


Thanks in advance for any thoughts or pointers - much appreciated!

 

[Misca]

Create a named range with an INDIRECT formula in it.

I named my range "ChartRange" and used the following formula:

=INDIRECT(Sheet1!$A$2)

There's a data validation in my A2 that let's me choose only named ranges to that cell.

Create a chart normally and once you're done right click the chart and choose "Select Data" and choose "Edit" for the Series.

For the Series Range use a formula =YourSheetName!ChartRange

This should do it: If you change the value in A2 the chart range changes.

 

[aiki100]

Hello Misca! Thank you so much for your pointers here - I think I am close, and hope to get a bit more clarity. I got this to work for one of my list ranges, but not for anything else yet. I am (surely) missing something. Questions below, thanks --


Create a named range with an INDIRECT formula in it.

I named my range "ChartRange" and used the following formula:

=INDIRECT(Sheet1!$A$2)


So this is creating a new named range using INDIRECT and points to what will be the data validation cell. Correct?


There's a data validation in my A2 that let's me choose only named ranges to that cell.

I think this may be my issue: "A validation list that only lets you choose named ranges" -- how do you accomplish this step please? (I have my list pulling from a lookup table.) This must be wrong -


Create a chart normally and once you're done right click the chart and choose "Select Data" and choose "Edit" for the Series.

I think the rest of this is good....thx!

For the Series Range use a formula =YourSheetName!ChartRange

This should do it: If you change the value in A2 the chart range changes.

Thank you again so much for being willing to help with this...!

 

[Misca]

INDIRECT is a function that converts text to range names: The names must be real range names (one word / no spaces etc.). You can use it in your worksheet formulas, not only in formula names. In this case you're using it in formula names because chart ranges don't allow formulas in them. But since they allow named ranges you can sort of trick them with the INDIRECT formula in names.

To make sure the data validation only allows range names you must first have your names in a list somewhere and then choose Data Validation => Allow:List and point to your list of names in the source. Just make sure all your names in the list are spelled the same way as in your names. If your named ranges are on different sheets still point your chart series data to the sheet where the INDIRECT is getting the named range name from.

If you still can't get it to work start with real simple names (Range1, Range2 etc.) and see if you get it to work that way.

Here's a short video on how to use data validation and named ranges. In the video she's using the named range as her data validation list but you can use normal ranges as your list as well.

 

[aiki100]

Misca:


Thank you for the video and other info — I am still missing something, so let me spell out my steps please, and you'll surely see where I have gone wrong. I've tried a number of permutations using your guidance, and am still not there, yet feel I must be quite close. Thanks again very much for taking your time to assist on this.




On Sheet1


I have 5 (discontiguous) columns, each with a range of data, 2 through 1196.
One column has a header row called, "P1_C_sum"
I clicked that entire column (clicking the column itself)
And entered a named range in upper left nav box of: "P1_C_sum", (and then adjusted the actual data range to be 2 through 1196).


So: I had a column of data with a header of: "P1_C_sum", and now have created a named range from that column (row 2-1196) with the exact same name, "P1_C_sum"


I then created named ranges this way for the rest of the columns for a total of 5 named ranges.


On Sheet2


I created a lookup (formatted at text) that lists each of these 5 named ranges, spelled exactly the same, in column V, one per row.
The lookup is laid out this way:


Row 1: "Validation List Chart" (header row)
Row 2: P1_C_sum
Row 3: P2_C_sum
Row 4: P3_C_sum
Row 5: P4_C_sum
Row 6: P5_C_sum


On Sheet3


I created a data validation box in Sheet3!$I$8, using list, and used the following formula, pointing it to the source of the lookup list:


=Sheet2!$V$2:$V$6


Next
I then created a named range using INDIRECT, called "EqC_Period"
The formula I used for this named range points to the validation box itself:
=INDIRECT(Sheet3!$I$8)


Also on Sheet3


I then took an already created line chart, right-clicked to access "select data."
I erased any series that was in the box, and entered a new (the only) series with the following:
=INDIRECT(Sheet3!EqC_Period)


This does populate a chart, but only with a reference to what ever the validation list had in its cell when I edited the series in the chart. Switching the validation list to a different item in the list produces no change in the chart.


Thanks again for whatever input you may have (I got quite a lot from your explanation of INDIRECT, thx!) - and if you think a pointer to other videos/posts, etc. or whatever you think is best, I will happily take them.


Many thanks!