Hello all -
I recently posted something similar here, and got some great help that got me 70% of the way, but I'm still stuck. Was hoping someone could pick up the thread and show me the error of my ways. Thanks!
Final result desired:
I have a chart on Sheet3, and wish to update that chart dynamically via a drop down validation cell — I have 5 different columns of data, and would like the chart to reflect the choice of whatever column is chosen in the drop-down list.
Here is what I have done:
A- I have 5 columns of data on Sheet1, and have created individual named ranges out of them all. (This will be the ultimate source for what will change in the chart.)
B- I then created a lookup on Sheet2 to be used with a validation list. I used the named ranges described above, typing their names in, exactly as in name manager (all one word, no spaces).
C- On Sheet3, I created a data validation box using list in $I$8, pointing it to the lookup on Sheet2. This all works as expected, so far.
D - I then created one more named range, called "EqC_Period" using INDIRECT to point to the data validation list (of the individual named ranges) on Sheet3. Like so:
=INDIRECT(Sheet3!$I$8)
E - Right-clicking the chart, and choosing select data, I deleted whatever was in the series currently, and created a new series (with the + sign) and entered:
=Sheet3!EqC_Period
And here I do get a chart - which upon inspection has converted itself to whatever the named range was in the validation box at the time I changed the series, but subsequent changes to the validation box produces no change in the chart.
So — Chart series should be (indirectly) pulling a named range from a data validation list, which itself (the validation list) lists the 5 actual named ranges of data on another sheet in the same workbook.
I feel like I have too much pointed to each other in too many places, but google and repeated experiments have gotten me nowhere.
Does anyone see what I am missing here, please?
Thanks so much for taking the time - I really appreciate it!
I recently posted something similar here, and got some great help that got me 70% of the way, but I'm still stuck. Was hoping someone could pick up the thread and show me the error of my ways. Thanks!
Final result desired:
I have a chart on Sheet3, and wish to update that chart dynamically via a drop down validation cell — I have 5 different columns of data, and would like the chart to reflect the choice of whatever column is chosen in the drop-down list.
Here is what I have done:
A- I have 5 columns of data on Sheet1, and have created individual named ranges out of them all. (This will be the ultimate source for what will change in the chart.)
B- I then created a lookup on Sheet2 to be used with a validation list. I used the named ranges described above, typing their names in, exactly as in name manager (all one word, no spaces).
C- On Sheet3, I created a data validation box using list in $I$8, pointing it to the lookup on Sheet2. This all works as expected, so far.
D - I then created one more named range, called "EqC_Period" using INDIRECT to point to the data validation list (of the individual named ranges) on Sheet3. Like so:
=INDIRECT(Sheet3!$I$8)
E - Right-clicking the chart, and choosing select data, I deleted whatever was in the series currently, and created a new series (with the + sign) and entered:
=Sheet3!EqC_Period
And here I do get a chart - which upon inspection has converted itself to whatever the named range was in the validation box at the time I changed the series, but subsequent changes to the validation box produces no change in the chart.
So — Chart series should be (indirectly) pulling a named range from a data validation list, which itself (the validation list) lists the 5 actual named ranges of data on another sheet in the same workbook.
I feel like I have too much pointed to each other in too many places, but google and repeated experiments have gotten me nowhere.
Does anyone see what I am missing here, please?
Thanks so much for taking the time - I really appreciate it!
