32458

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!

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)

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.

<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

Recommend

  • How to manually link in Brightway2 an imported exchange, given I have found the correct one in ecoin
  • how to solve error of jsp page?
  • Remove Specific key value pair from PHP array
  • Variable scope in Python nested function [duplicate]
  • Removing Parent in AS3: Does this free the memory used by all the children?
  • Unresolved external symbol (OpenGL and c++)
  • How can I fix g++ Undefined symbols for architecture x86_64 error?
  • Elastic Beanstalk failed to update local Git configuration - Windows
  • Accessing variables in other classes (Java)
  • pandas mix position and label indexing without chaining
  • Android Studio: Link of external JAR in a multi modules project
  • Writing an RDA to CSV in R
  • How to make a div appear under button
  • cast stl::vector containing pointers to stl::vector containing constant pointers
  • QObject::findChild() returns None without obvious reason
  • How to solve “The hierarchy of the type is inconsistent” error in Java Eclipse?
  • How do I know what reference to include to import a specific .NET namespace?
  • Google Player Game Service Error: Can't build the sample game
  • How to name a 'group' check box in Adobe Reader when wanting to fill form by FDF / XFDF
  • Add reference to ASP.NET 5 Class Library from Framework 4.5 Class Library Project
  • Angularjs pass function from Controller to Directive (or call controller function from directive) -
  • How to revert to previous XCode version?
  • Ajax calls do not work in IE unless you fiddle with security settings
  • Keep this build forever option - Jenkins
  • How to avoid particles glitching together in an elastic particle collision simulator?
  • Cannot resolve symbol 'MyApi'
  • Seeking advice on Jetty HttpClient Hang
  • Recording logins for password protected directories
  • Lost migrations and Azure database is now out of sync
  • Splitting given String into two variables - php
  • Check if a string to interpolate provides expected placeholders
  • How to show dropdown in excel using jrxml (jasper api)?
  • Adding custom controls to a full screen movie
  • Google cloud sdk not working when python points python3
  • Windows forms listbox.selecteditem displaying “System.Data.DataRowView” instead of actual value
  • Error creating VM instance in Google Compute Engine
  • using HTMLImports.whenReady not working in chrome
  • -fvisibility=hidden not passed by compiler for Debug builds
  • How can I remove ASP.NET Designer.cs files?
  • Android Heatmap on canvas or ImageView