?
Solved

Allow user to change the value of a cell (named range) from multiple places in workbook

Posted on 2013-11-01
6
Medium Priority
?
201 Views
Last Modified: 2013-11-07
Yesterday I asked a very similar question to this and have a solution. However, I can't quite figure this one out either. I am building a forecasting model and want the user to be able to change UnitPrice in more than one place in the workbook. For example there is a scenario page which is where the UnitPrice currently resides. I would like to allow them to change UnitPrice on the P&L page as well. This would obviously make it more user friendly by not requiring them to toggle back and forth to simply change the price. Thanks for any help or suggestions.

Dale

Link to previous question: Very similar question
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
  • 3
  • 3
6 Comments
 
LVL 12

Expert Comment

by:Harry Lee
ID: 39617303
dlogan7,

My solution for the other question would work for you in this case as well.

Can you tell me the sheet names which these named range "UnitPrice" is sitting on? I can reconfigure the macro for your solution.

Or even better, if you can upload a sample file with dummy data so that I know the exact structure of your workbook.
0
 

Author Comment

by:Dale Logan
ID: 39617750
UnitPrice will be moved to a sheet named MiscCalcs. It is not in a state for me to load dummy data just yet. Thanks.
0
 
LVL 12

Expert Comment

by:Harry Lee
ID: 39617792
dlogan7,

I have other questions as well. Just wondering if there the UnitPrice is 1 single cell or it is like a quotation, one of the column contains more than 1 unitprice?

Also how many sheets is going to be in the workbook?
0
Industry Leaders: 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!

 

Author Comment

by:Dale Logan
ID: 39630248
Sorry for being so delayed in getting back to this. The unit price will be a single cell. There are currently 5 sheets in the workbook. Thanks.
0
 
LVL 12

Accepted Solution

by:
Harry Lee earned 2000 total points
ID: 39631332
dlogan7,

Please look at the sample I have created for this question. It's is very similar to the one I have provided to your last question.

Please pay attention to the followings so that you can fully understand how the linkage works.

1) Marcos sitting in ThisWorkBook in the VBA Editor
2) Named Ranges in Name Manager

Implementation Steps

1.

Copy all the codes in Thisworkbook in VBA Editor and paste into the real workbook Thisworkbook you are hoping to automate.

2.

In VBA editor, look at the code in your real workbook, configure the Name of the Name Range
eg. SrhString = "UnitPrice"
Change UnitPrice to whatever you want, as long as it's equal to the Name you have given to your named ranges.

3.

Go into Name Manager, and setup the Named Ranges properly.
Please be sure when you setup a named range, your scope is only the sheet where the specific named range is sitting in but not the whole workbook.
This way, you can have the same name eg. UnitPrice for the name ranges on all the sheets you want.Link-Cell-Sample1.xlsm
0
 

Author Comment

by:Dale Logan
ID: 39631501
Perfect. Thanks so much.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

752 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