Selection choice in Excel, How to go about it?

[gilbert]

Hi Everyone,

I tried to create a simple worksheet containing 3 option choices, I
want the data of the choice picked by the user be reflected in the
other cells. To illustrate the picture better, I hv enclosed a simple
workbook here....

Could anyone give me some thougths on how to go for it.....the simple
the better....

Attachment filename: sales.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=409669

 

[Nicky]

Hi Gilbert,
is this what you're after? (you could hide the offset in cell E4, o
move it to another sheet, for neatness)

 

[Nicky]

sorry, forgot the attachmen

Attachment filename: reply.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=40978

 

[gilbert]

Hi Nicky,

Yes...that's what I am looking for.....but how shall I go about it? How
do I assign the linkage for offset...I am sure there is linkage between
the data and offset, right?

Please help to explain.

 

[Nicky]

Right click on one of the option buttons, choose the 'control' tab and
set the address of the cell to link the control to (I used E4, but it
can be anywhere in the workbook). This will return a value of 1 if the
first button is checked, 2 if the second, and so on, for each button on
the sheet.

Offset tells excel where to copy values from, relative to a particular
cell. So the formula
=offset(A1,1,0) returns the value in cell A2 (1 below A1)
=offset(A1,0,2) returns the value in cell C1 (2 right of A1)

Because the values you want to be able to choose are on successive rows
of your sheet, this formua can been used to set the 'offset' in the
formulas in cells E13:I13.

In the example, the value returned by the option buttons to cell E4 is
used to determine how many rows below row 7 to get the data from.
Because you want an offset of 0 for Optionbutton1, 1 for Optionbutton2
etc, (one less than the value of cell E4), the formula is
=OFFSET(E7,$E4-1,0)

You should adjust this formula depending on where on the sheet your
results line is (eg E13:I13) relative to your input lines (eg E7:I10).

Hope this helps