Which component to use to copy ranges of data
I would like to create a dashboard which will help to compare several countries with the worldwide results of a survey. I have the following set-up:
Cat 1_____5%______ 4%______ 3%______ 12%
Cat 2_____8%______ 4%______ 9%______ 7%
Cat 3_____4%______ 2%______ 6%______ 3%
The data of A should always be shown in a bar chart. The user should be able to select B-D to show its data as well and thereby compare it with A. Furthermore, multiple selection should be possible so that I can compare A with B and C at the same time.
Therefore, I created the bar chart referring to
Cat 1____ 5%
Cat 2____ 8%
Cat 3____ 4%
and now I want to enable the user to somehow select B, C and/or D to copy the data down into the empty columns and thereby show it in the bar chart. But my problem is that with a check box (and any other component to select), only one value (usually 0,1) can be copied. What I need is either copy a set of values or delete the target cells. And as macros are not working either, I have no clue how to realize that.
Does anyone has suggestions?
Are B, C, and D always the same data?
If so, you can use the Check Box not to insert a value but as a conditional to control visibility:
Check Box B - On
B1 = 0/1 (from Check Box)
B2 = IF(B1=1, data, "")
B3 = IF(B1=1, data, "")
and so on for the other columns.
If instead you mean you have 3 colums and a plethora of selectable data values, you'll need to setup a fancier selection formula using OFFSET in conjunction with a list selectors, like Combo Boxes.
Combo Box inserts its position. OFFSET looks up and inserts based on that position.
e.g. Combo Box selection = New York (position 4) insert to A1
B1 = OFFSET(AA1:AZ10, 1, A1)
(looks at data range, first row, 4 columns over [column being New York])
B2 = OFFSET(AA1:AZ10, 2, A1)
(looks at data range, second row, 4 columns over)
and so on. Same setup for column C and column D.
I hope this makes sense.