Change variable from multiple sheets?

Question

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!

Answer 1

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

Answer 2

Named range, option 1: "override" style formula

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)

Where oilprice1 and 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.

Named range, option 2: scroll bar or spin control

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.

Checkboxes across sheets

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.