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

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
Dale LoganConsultantAsked:
Who is Participating?
 
Harry LeeConnect With a Mentor Commented:
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
 
Harry LeeCommented:
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
 
Dale LoganConsultantAuthor Commented:
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
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.

 
Harry LeeCommented:
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
 
Dale LoganConsultantAuthor Commented:
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
 
Dale LoganConsultantAuthor Commented:
Perfect. Thanks so much.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.