Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Which component to use to copy ranges of data

Hello all,

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:

_________A________B________C________D

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

_________A________B________C________D

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?

Best regards,

Marco.

replied

Marco,

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.

<br>

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.

<br>

I hope this makes sense.

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question