Solved

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

Posted on 2013-10-31
6
190 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:dlogan7
6 Comments
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
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:dlogan7
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 21

Accepted Solution

by:
Ejgil Hedegaard earned 300 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 200 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:dlogan7
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
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 will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

911 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now