Solved

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

Posted on 2013-11-01
6
194 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
[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: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
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!

 

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

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!

Question has a verified solution.

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

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
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 in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

749 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