Option Buttons and Code

[teebird]

Hi All

I have a number of lookup tables (named ranges) on Sheet3

Named Range Column 1 - Column 2 - Column 3
Admin . . . . . . . . .AO1 . . . . .0 . . . . . . .50
. . . . . . . . . . . . . AO2 . . . . 51 . . . . . 100
Prof . . . . . . . . . .P1 . . . . . .0 . . . . . . . 50
. . . . . . . . . . . . .P2 . . . . . 51 . . . . . .100

Sheet 1 I have a form. What I want is a formula so that when a number is entered into Cell B2 on Sheet 1, Excel will first match the option selected to the corresponding named range on Sheet 3 eg. Admin. Then it will look through the range to match the number entered (76) to a range of numbers. My option button is called "Forms.OptionButton.1"

Sheet 1
Cell B2 = 76
Option button choice selected = Admin

Excel will find the named range Admin then look for where the 76 is and return the value in the 1st column - in this case AO2.

Any assistance would be greatly appreciated. I have no idea where to start. I am thinking a lookup function but the option button has lost me.

thanks Tee

 

[SkipVought]

Tee,

1. It would sure help if your EXAMPLE corresponded with the given data provided.

2. You lookup example makes absolutely no sense.

What is the data in Column 1, Column 2 & Column 3?

Where does 76 come in?

Please answer each of these questions clearly, concisely and completely.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[teebird]

Apologies for the confusion. I have included a sample spreadsheet - hope this helps. Many thanks for your assistance.
Tee.

 

[SkipVought]

Where is your oprion button?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

If you

1. use a set of Forms option buttons Admin & Prof, where the linked cell is Named obPosition

2. Set up your lists as such, using the list heading as the Named Range
[tt]
Nindex Pindix
Admin =MATCH("Admin",Position,0)
Prof

Position Level Points
Admin AO1 0
Admin AO2 51
Admin AO3 101
Prof P1 0
Prof P2 101
Prof P3 201
[/tt]
then the lookup formula is
[tt]
=INDEX(Level,MATCH(M6,OFFSET(Position,INDEX(Pindix,obPosition,1)-1,2,COUNTIF(Position,INDEX(Nindex,obPosition)),1),1),1)
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

I finally found your option buttons.

I perfer using the Form ob, because I get a NUMBER in the linked cell indicating which ob is selected.

I structure my NIndex list to be ordered accordingly.


This is a non VBA solution.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[teebird]

Hi Skip

Many thanks for your reply...sorry ...I am a bit lost.

I have replaced the option buttons with Form Option buttons and linked them to obPosition (Cell M6). When I changed option buttons now - I get either a 1 (Prof) or a 2 (Admin)in Cell M6.

I have setup my named ranges like you have with the List Heading as the Named range.

Where does the =Index formula go

I have it in Cell O6 but I get the #NAME? error. I should have AO2.

Cheers Tee

 

[SkipVought]

I have replaced the option buttons with Form Option buttons and linked them to obPosition (Cell M6). When I changed option buttons now - I get either a 1 (Prof) or a 2 (Admin)in Cell M6. Make it some other cell off to the side. M6 has your lookup Point value.

The linked cell obPosition, contains an index number value indicating which option button is selected. This is NOT M6. M6 is your lookup Point value. Put obPosition out of the way somewhere.

I have setup my named ranges like you have with the List Heading as the Named range.

Where does the =Index formula go

The Index formula is in N6 (Level)

I have it in Cell O6 but I get the #NAME? error. I should have AO2.

#NAME? indicates that you have an undefined Named Range.

For each of the two tables I posted, use this process...
[tt]
1. Select the headings and data
2. Insert > Name > Create -- Create names in TOP row
[/tt]
You must ALSO include the data for EACH POSITION in both these tables, not just Admin & Prof!


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[teebird]

Hi Skip thanks for your perseverance...

I have uploaded another spreadsheet - I think I have done anything. But still get the #NAME? error.

Cheers. Tee.

 

[SkipVought]

1. Your NIndex values have trailing spaces. Remove the spaces.

2. In the N6 INDEX formula, change PIndix to PIndex.

3. Your Named Ranges in the Position, Level, Points table are INCORRECT. Use the process I enumerated in my previous post on 19 Mar 10 22:28, using Insert > Name > Create...
BTW, when you construct formulas using named ranges, you can hit F3 to insert named ranges from a list.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[teebird]

Hi Skip

Have removed the spaces,corrected the spelling, as I am using Excel 2007 so I cannot named the ranges as you suggested. I have tried a couple of ways to name them but I still get errors.

when I click option button Prof - I get #VALUE! in Cell N6, when I click option button Admin - I get #REF! in Cell N6.

Many thanks for your help...we are getting closer to fixing this.....lol. Tee

 

[SkipVought]

"I am using Excel 2007 so I cannot named the ranges as you suggested. "
Formulas TAB > Create from selection.

Of course, you must have previously selected the table as formerly instructed.

If you still have problems, post your workbook in the previously posted site.



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[teebird]

Hi Skip

I did the name ranges as you suggested.

Now - When I select PROF (option button) - I get #VALUE! in Cell N6

and when I select ADMIN (option button) - I get P1.

I should be getting AO2.

I have uploaded another file. MY-Table-21-03.xls

Thanks again Tee.

 

[SkipVought]

I left out a singnificant part...
[tt]
=INDEX(OFFSET(Position,INDEX(Pindex,obPosition,1)-1,1,COUNTIF(Position,INDEX(Nindex,obPosition)),1),MATCH(M6,OFFSET(Position,INDEX(Pindex,obPosition,1)-1,2,COUNTIF(Position,INDEX(Nindex,obPosition)),1),1),1)

Here's how it breaks down...

=INDEX
(
OFFSET
(
Position,
INDEX
(
Pindex,
obPosition,
1
)-1,
1,
COUNTIF
(
Position,
INDEX
(
Nindex,
obPosition
)
),
1
),
MATCH
(
M6,
OFFSET
(
Position,
INDEX
(
Pindex,
obPosition,
1
)-1,
2,
COUNTIF
(
Position,
INDEX
(
Nindex,
obPosition
)
),
1
),
1
),
1
)
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}