Solved

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

Posted on 2013-11-01
6
175 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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 …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

863 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

19 Experts available now in Live!

Get 1:1 Help Now