Checking a box(?) to update formula range.

[lawi227]

I'm interested in modifying the below equation:


=VLOOKUP($A$1,'High Level Costs, Capital'!$A$21:$I$35,MATCH($D3,'High Level Costs, Capital'!$A$21:$I$21,0),FALSE)*F3

I would like to have a check box or some variation (I'm open to recommendations). Example below:

Source 1	X
Source 2

<tbody>
</tbody>

Let's say I check the box labeled "Source 1". I would want the formula above to use the value in F3 (as highlighted and bolded in red). If I put a check in the box labeled "Source 2" then I would want use the value in G3.

This formula is listed in T3:T67.

Note: I have worksheets with identical organization and format (e.g., all the formulas are in the same boxes), but only the data is different. I would like that requested check box feature to apply to all these worksheets so that the data sources change on every worksheet. The worksheets are called: 2009 Summary, 2010 Summary, 2011 Summary, 2012 Summary, 2013 Summary, 2014 Summary, 2015 Summary).

I prefer not to use VBA or any advanced coding if possible.

Let me know if I can provide further information. Thanks in advance for your help and furthering my Excel knowledge.

 

[peterhinton]

use an option button,

developer > insert > option button,

then format the control and link it to a cell,
add more option buttons etc, all linked to the same cell,

Then when a different option is selected the cell with have a different output

then use *INDIRECT(VLOOKUP(J5,O12:P13,2)) at the end of your formula instead of *f3

, J5 is the cell I used to link the option buttons,
O12:P13 is small table with values like below:

1 F3
2 G3

just change to suit your needs,

hope I made sense

 

[lawi227]

Am I able to use 1 option button to update all cells (T3:T67)? I don't want 64 options buttons that have to all be clicked to update the formula.

Also the formula in T3 would pertain to the value in F3; T4 would pertain to F4, etc. etc. Would the indirect feature account for this?

I'm just now about to build out this formula and make your recommended changes, so I will see if I run into any other issues.

Thanks!

 

[peterhinton]

you should only need one option button activated,

=VLOOKUP($A$1,'High Level Costs, Capital'!$A$21:$I$35,MATCH($D3,'High Level Costs, Capital'!$A$21:$I$21,0),FALSE)*INDIRECT(VLOOKUP($J$5,$O$12:$P$13,2))

use the $ to lock the references in, and then it will work across all of T3:T67,

Just ensure you have the table set up in O$12:$P$13, well adjust it to where you have the table etc.

 

[lawi227]

Thanks. This is helpful.

I did some research on radio buttons (the option button). I think I would like to have two radio buttons grouped together. One radio button will select a different data source (lets say data source 1 which relates to data in column F.) The other radio button would pertain to data source 2 which relates to column G.

Can I do that instead of creating this table you discussed above? Do you have any advice on how to do that?

 

[peterhinton]

as far as my knowledge of using the option buttons goes is, they only return a value, I usually use this value as a reference point to get to what I want from the button,

(my knowledge of using these only started last week though to be honest :s )