Solved

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

Posted on 2013-10-31
6
200 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
ScreenConnect 6.0 Free Trial

Explore all the enhancements in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

 
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

ScreenConnect 6.0 Free Trial

Check out the updates in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI that improves session organization and overall user experience. See the enhancements for yourself!

Question has a verified solution.

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

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

832 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