• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Changing the value returned by an option button w/o VBA???

D

DashboardNovice

Member
When you add the first option button the value returned to a cell is 1 if it is selected. The second option button added will return a value of 2 if selected, etc.

But instead of returning 1, suppose I want to change the value returned to 15. Is there a way to do this without VBA?
 
No, It can only be done using VBA

But you can use spreadsheet formulas to define what happens when the 2 button is pressed
eg: =if( ButtonReference = 1, do something, do something else)
 
Hui said:
No, It can only be done using VBA

But you can use spreadsheet formulas to define what happens when the 2 button is pressed
eg: =if( ButtonReference = 1, do something, do something else)
Click to expand...

Ok. Sometimes I forget the obvious. You just gave me a great idea, although it doesn't involve the IF function.

Here is my formula:
=INDIRECT(VLOOKUP($C$12,$B$14:$C$16,2,0)&"!"&"A2")

I want the result of the VLOOKUP to spell out a reference to cell A2 on a sheet called 'BA' like this: =BA!A2

So ultimately, this formula will go to cell A2 on a different sheet, a sheet called 'BA' and what ever is in cell A2 on the BA sheet, drop it into the current cell on the current sheet. But here is the problem I am running into.

While this works in the current cell, when I drag this across columns I need A2 to update to B2, then C2, etc. But since I have quotations around A2, this means that I will always see A2 when I copy across.

I tried removing the quotes from A2 and using the INDIRECT function, but it didn't work, unless I did it wrong. Any ideas?

This would save me from having to write VBA code. Again, thank you Hui for leading me to this idea. I'm very close to my solution.
 
You must log in or register to reply here.
Back
Top