Interactive Excel Chart: get values of multiple columns based on dropdown selection

0
votes

I'm trying to create an Interactive Chart that will show the trend of a selected value from a dropdown list

Here's where im getting the data from, sheet name is Calculator, i listed my dropdown values in column AH Calculator

and here's the chart which i'm trying to make interactive. It currently shows data for 'Compassion' (columns G:I). What i want to happen is get the chart to show also the data for Acknowledgement(columns M:O), Response(columns S:U), Expectation and Summarization. I've created Name Ranges which i know i can use to set which cells my chart will get its data from. what i cant figure out is how to set a Name Range that will get the values from columns G(G2 all the way down), H(H2all the way down), and I(I2 all the way down) when the dropdown is set to 'Compassion'; values from columns M(M2 all the way down), N(N2 all the way down), and O(O2 all the way down) when the dropdown is set to 'Acknowledgement; and so on.

Chart

Here's a screenshot of my Name Manager. the formula i used to get values of column G is =OFFSET(Calculator!$G$2,0,0,COUNTA(Calculator!$G:$G) - 1), this formula only works for one column though, i need a formula that will go through multiple columns

name manager

I tried using =OFFSET(Calculator!$G$2:$I9,0,0,COUNTA(Calculator!$G:$I) - 1) but when i test it manually, i get this: I'm really confused and need help on how to get this to work.

not working

Thanks in advance!

1
excelrangenamed
I would use a calculation that included each column and then each column has a binary multiplier which is controlled by selection. This would allow you to include as many, or few, as you wish. - Solar Mike
@SolarMike, can you please show me an example how that's done? - KarenDP

1 Answers

0
votes

i got it working now. I made a total of 3 Name Ranges(for Developing, Exceeding, and Meeting). Below is the formula i used to get the Developing Values. I just changed the reference cells for the Meeting and Exceeding Name Ranges. Thanks.

=IF(Calculator!$A$1=1,OFFSET(Calculator!$J$2,0,0,COUNTA(Calculator!$J:$J)-1),IF(Calculator!$A$1=2,OFFSET(Calculator!$P$2,0,0,COUNTA(Calculator!$P:$P)-1),IF(Calculator!$A$1=3,OFFSET(Calculator!$V$2,0,0,COUNTA(Calculator!$V:$V)-1),IF(Calculator!$A$1=4,OFFSET(Calculator!$AB$2,0,0,COUNTA(Calculator!$AB:$AB)-1),IF(Calculator!$A$1=5,OFFSET(Calculator!$AH$2,0,0,COUNTA(Calculator!$AH:$AH)-1))))))