Radio Button assistance

[jasonmcbride]

I have created a simple example of what I want to achieve in Excel 2007 and attached it here for you too see.
I have used radio buttons before but I just can't seem to get my head around this one. Note - there are 2 sheets. Please Help!!! see attached

[BigBas]

Can you possibly provide the sample in .xls format? I know there are a number of members here who don't have Excel 2007.

[jasonmcbride]

Yep, sorry I thought most people would have d/loaded the compatibility pack by now. http://www.microsoft.com/downloads/d...displaylang=en

I will save it as an .xls and upload it soon

[jasonmcbride]

Now attached as an xls

[jasonmcbride]

My friend told me that it is impossible to do what I want, is that right?

[Andy Pope]

You need to group your option buttons so you can record each choice within Gender, Paint and type.

With the 3 pieces of information you can create the row and column indexes needed to lookup the value.

I have named the table on the second sheet PRICES and used this in the formula.

The table on the main sheet if there to easily check the selection returns the correct price.

zip file contains xls and xlsx versions

[jasonmcbride]

Thanks Andy, that is Perfect, you're a legend. One question, could you please explain the formula in cell M4.
Thanks mate, I appreciate it.
Jase

[Andy Pope]

The formula calculates which column to use.
You have 3 sets of a pair of columns.

=(('Selection Sheet'!L1-1)*2)+'Selection Sheet'!M1

L1 defines which set of pairs so in order to determine where that starts we need to multiple it by 2. Doing that would actually places as beyond the set we need so we deducte 1 before multiplying. This places us at the end of the previous set.
Then in order to get the correct item from the pair we add the pair selection which is in M1.

So for the Mens Metallic you would get

((1-1)*2) + 2
(0*2)+2
0+2
2