?
Solved

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

Posted on 2013-10-31
6
Medium Priority
?
221 Views
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.

Dale
0
Comment
Question by:Dale Logan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 50
ID: 39615240
Hello,

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

cheers, teylyn
0
 

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.
0
 
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.
Link-Cell-Test.xlsm
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 23

Accepted Solution

by:
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.
Combobox-link.xlsx
0
 
LVL 12

Assisted Solution

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

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

Please test this out.
Link-Cell-Test.xlsm
0
 

Author Comment

by:Dale Logan
ID: 39616529
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

Introducing Priority Question

Increase expert visibility of your issues by participating in Priority Question, our latest feature for Premium and Team Account holders. Adjust the priority of your question to get emergent issues in front of subject-matter experts for help when you need it most.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

743 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