Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 231
  • Last Modified:

Allow user to change a cell (named range) value from multiple places

I don't do much in Excel, but in Access I use TempVars. That's sort a value that travels throughout the app. I am building a model in Excel for a client and am looking to do something similar. I currently have a named range called ActiveScenario and it's a single cell that is using Data Validation for it's value. I was wondering if there is a way to have another location (cell) that does the same thing. Thanks for any help and/or suggestions.

Dale
0
Dale Logan
Asked:
Dale Logan
2 Solutions
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

"does the same thing" as what? Have another named range? Can you explain a bit more?

cheers, teylyn
0
 
Dale LoganConsultantAuthor Commented:
Sorry about that. So, right now the only way a user can change "ActiveScenario" is to navigate to the sheet where I have a drop down. I use the value of  "ActiveScenario" in formulas throughout. I was wondering if there was a way to not require the user to navigate back to that other sheet. It's sort of like a global value that can be change from multiple places. Hope that make it more clear.
0
 
Harry LeeCommented:
Check out this sample.

The Cell A1 is linked on all sheet. No matter which sheet you change, the rest will follow.

It's a Workbook event on WorksheetChange.
Link-Cell-Test.xlsm
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Ejgil HedegaardCommented:
You could use a Formular Combobox on each sheet.
If all use the same input list, and link to the same output cell, all will update when one is changed.
The link cell holds the position (number) on the list, and then ActiveScenario cell could be set to the real value, using the Index function refering to the Input list and the link cell with the position on the list.
Name the input list and the link cell, then it is easy to reference in the comboboxes, and in the index function.
See attached workbook with an example.
2 sheets and 2 comboboxes, when one is changed, the other change, and the output (ActiveScenario) also change.
Combobox-link.xlsx
0
 
Harry LeeCommented:
dlogan7,

I have updated the code in the ThisWorkbook for WorksheetChange event.

Please test this out.
Link-Cell-Test.xlsm
0
 
Dale LoganConsultantAuthor Commented:
I've tested both options and like them both.

hgholt,

Question: no matter which sheet you make the change on, you always end up on the last sheet. How would you fix that?

Harry,

I had no idea you could put a combo box in Excel and not need to use VBA. That's why I've been subscribed to this site for years. Everyone makes me look way smarter than I really am.

Dale
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now