I have an excel workbook with multiple sheets that aggregate costs and revenues of different technological components (set up in different sheets) in a system.
I want to have a main worksheet, where users can change a small selection of important variables from the technology sheets. I also want those important variables to be defined and editable on the technology sheets.
I've been using named ranges to manage variables, but I'm not sure how to link two cells on different sheets to one variable. For example, I want to name a variable "oilprice" that is referenced in different formulas. I want to be able to change the variable "oilprice" from the main worksheet and the electricity technology sheet in my workbook.
Similarly, I want to be able to check a box on both sheets for "Turn on Electricity" and have the checkbox on the other sheet change as well.
I've been looking around on google and stackoverflow but can't find an answer. Thanks!Answer1:
<h2>Named range, option 1: "override" style formula</h2>
With named ranges, you are not able to update the value from multiple cells. You could use logic in a formula to look at a "override" cell and pass that value to the actual named range. This works if the number of overrides is small. That style of formula looks like:
=IF(ISBLANK(oilprice2), oilprice1, oilprice2)
oilprice2 are the cells that hold possible values. Note that there is an implied order in these which can get confusing after a while. That is, if
oilprice2 has a value, it will not change
oilprice1 nor will
oilprice1 be considered.
<img alt="named range override" class="b-lazy" data-src="https://i.stack.imgur.com/2WlFg.png" data-original="https://i.stack.imgur.com/2WlFg.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" /><h2>Named range, option 2: scroll bar or spin control</h2>
Another option similar to the checkboxes below, is to use a spin control or scrollbar control to update the values. Those work across multiple sheets.<h2>Checkboxes across sheets</h2>
For the checkboxes, this is handled by the
Cell Link. You can set as many checkboxes as you want to control a single cell's value.
Here is an example with two checkboxes sharing the same
Cell Link = $C$2. They both change when one is clicked.
<img alt="enter image description here" class="b-lazy" data-src="https://i.stack.imgur.com/z8wAZ.png" data-original="https://i.stack.imgur.com/z8wAZ.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" />Answer2:
Okay, I figured out how to have two cells to refer to the same value.
I named a range "oilprice" on the "electricity" sheet.
The cell to input oilprice on the "main" sheet has the formula "=oilprice" and is named "oilprice2", showing the value on the "electricity" sheet "oilprice" named range.
Then I made the following vba code which updates the "oilprice" cell on the electricity sheet when you change the "oilprice2" cell on the main sheet and reverts back to the formula showing "oilprice":
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Target.Worksheet.Range("oilprice2")) Is Nothing Then Application.EnableEvents = False Worksheets("electricity").Range("oilprice").Value = _ Worksheets("main").Range("oilprice2").Value Worksheets("main").Range("oilprice2").Value = "=oilprice" Application.EnableEvents = True End If End Sub