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

Posted on 2013-10-31
Medium Priority
Last Modified: 2013-11-01
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.

Question by:Dale Logan
LVL 50
ID: 39615240

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

cheers, teylyn

Author Comment

by:Dale Logan
ID: 39615272
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.
LVL 12

Expert Comment

by:Harry Lee
ID: 39615491
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.
Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

LVL 25

Accepted Solution

Ejgil Hedegaard earned 1200 total points
ID: 39615551
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.
LVL 12

Assisted Solution

by:Harry Lee
Harry Lee earned 800 total points
ID: 39615622

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

Please test this out.

Author Comment

by:Dale Logan
ID: 39616529
I've tested both options and like them both.


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


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.


Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Manually copying shapes and their assigned macros one by one to a new location can be tedious, but if you use the Excel utility workbook attached to this article, the process will be much quicker and easier.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

597 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question