Solved

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

Posted on 2013-11-01
6
163 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:dlogan7
  • 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:dlogan7
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:dlogan7
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 500 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:dlogan7
ID: 39631501
Perfect. Thanks so much.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
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…

708 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

13 Experts available now in Live!

Get 1:1 Help Now