Output option buttons as text to new sheet

[johnselekta]

Hi guys

I have an array of option buttons in a group (named Agegrp, located on Sheet1). When an option is selected, I have this linking to a second sheet recording the data (cell = D3, worksheet = datacollection).

Currently, my 6 options are coming out numbered 1-6 instead of their respective age ranges. How can I make this record the label entry not the button number?

1 = <30
2 = 31-40
3 = 41-50
4 = 51-60
5 = 61-70
6 = >70

Got a seriously hard time on my hands today, so any help is a big help! Thanks

 

[VoG]

One way would be to create a lookup table (like the one you posted) then use VLOOKUP to return the age ranges in a separate column. See http://www.contextures.com/xlFunctions02.html

 

[johnselekta]

Cheers for that, never heard of VLOOKUP, looks easy enough! Got another couple of questions, will make them in a new thread.

 

[johnselekta]

Right then the plot thickens! I found I can just use this to read one value

=IF(datacollection!D3=1,"<30")

So how would I chain this to 6 different numbers instead of just a single entity? I'm new to all this but can code a bit, so am still getting my head round the syntax

 

[VoG]

If you want to avoid having a lookup table try

=CHOOSE(Results!D3,"<30","31-40","41-50","51-60","61:70",">70")

 

[johnselekta]

Ah brill I just worked out a solution but I'm gonna use yours!

I had

=IF(datacollection!D3=1,"<30",IF(datacollection!D3=2,"31-40")) etc etc

But yours seems to be much slicker! THANK YOU so much.

EDIT: it is indeed an awesomely simple solution, I salute you!