New Macro for Calculating Dollar Value against a Percent

EE Pros,

 This is the second of three requests associated with the same WS.

 I have a simple WS that currently allows someone to select (via a Check Mark) a row, and what I'm looking for is having the ability to "toggle" between a % input and it's $ equivalent.  I need a button that fires a macro that toggles between a % that is inputted in a cell / table, and then calculates the $ value based on a value that is stated.  The File Attached has specific instructions that better explain what I'm looking for.

The password on the WS is "pass"

Thank you in advance.

B.
D--Data-Data-Temp-Selection-Display.xlsm
Bright01Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
In the workbook you say in one place that ".  IF there is no "P" (Checkmark) in Column A then show a blank in the % Table" but in another you say "When the Macro fires, the value in this cell for example should be $60,000. When the button is pressed again, it should go back to 30%" which seems contradictory.
0
Martin LissOlder than dirtCommented:
Sorry but I think I need to back out of this question since it has a lot of complexity that I don't understand. For example if I change E8 to 36000 dollars that changes E3 and E12 to a number in the billions and I don't know how to handle that since I got 36000 by multiplying E3 by the precent in E8. (And I just stupidly lost the code I had written!)
0
Bright01Author Commented:
Martin,

NOOOOOO.  Don't let me lose your talent on this!  I made a mistake.  I didn't take out the current formulas so you were getting erroneous errors.

Here's what I'm trying to do;

Input % values that make up values related to each row. Select a row with a Check (Col. A).  This works fine.

The row that is selected gets included in a calculation that is in the % Table and is the % * the indicated $ value from the question at the bottom of the rows (Cell C12 in this case)
The calculation is showing a TOTAL from the %s selected in the Three Summary Cells at the bottom (i.e. Row 12) - This is not working or coded yet.

Finally, when you fire the button MACRO, it should change the % values in the Table, to the actual $ values it has calculated with that single %.  (So your total may be $100,000 in the "Conservative" Column, but it is made up of the Sum of all the dollars calculated by the individual % values).  Hit the Button again, and it goes back to the % values you can input or change.

I've fixed the File.  Please take a look.  You're one of the best EE Pros I work with.

B.
D--Data-Data-Temp-Selection-Display.xlsm
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

Martin LissOlder than dirtCommented:
OK, let me take another crack at it.

Some questions:
1.    Are there always just 2 groups of data?
2.    Are the number of rows in each group always 7 in the first group and 8 in the second?
3.    Do the 1's in column D have any impact on what you want me to do?
4.    When the cells are changed to show dollars, do you want the the dollars to be displayed with a space after the dollar sign like you have in cells C12 and C21?
0
Bright01Author Commented:
Martin,

"Thanks!"

1.) There will actually be about 5 groups but I thought if I got two from EE Pro, I could figure out how to scale it.
2.) No.  I thought I'd use "Range Names" to reference the actual Groups. You will see two Ranges in the example I sent to you.  This should also allow me to add rows in the future and the Macro should work.
3.) NO.  The 1s in D are from a "Union" function in a Macro and was previously used as a Helper Cell.  I'm planning to take that out.
4.) Sure.  That would be fine.

Hope that helps.  Sorry to be so confusing on this.  I think this will be a rather "neat" app. when it's completed.

B.
0
Martin LissOlder than dirtCommented:
Thanks. That help a lot. Just so that I can mention it in some documentation I'm including in the macro, would it be proper for me to say that there are currently two "Assets" shown on the Assets sheet?
0
Bright01Author Commented:
Actually, neither are "Assets".  They are actually "Use Cases" and the table rows are "Sub-Use Cases".

Thanks for asking.

B.
0
Martin LissOlder than dirtCommented:
Just a thought. Why have the button at all and instead include both the $ and % values in the table?
0
Bright01Author Commented:
Great question.  

This tool/WS is used to present to clients.  The first step is to get their agreement as to the % of contribution they are receiving from a particular capability (i.e. "sub-use case"). Then, at some point, you switch the conversation to how those benefits translate into Dollar value.  I could have two cells that cover both; but what I love about Excel, is you can build in not only the information but the process by which insight is delivered.

Make sense?

B.
0
Martin LissOlder than dirtCommented:
Yes.

Cells F6 and G6 contain formulas that refer to cell D8. Should I replace them with formulas that look like the formula in E6? Actually it would be better if you told me what the formulas should be.
0
Bright01Author Commented:
Yes.  Good catch.  The Helper cells won't be active.

Thanks,

B.
0
Martin LissOlder than dirtCommented:
I haven't yet done the "sum total at the bottom" part but let me know if what I've done so far is correct.
The blue button now has a macro attached to it.

In my testing I found a potential problem with the existing code. The problem is that the Worksheet_BeforeDoubleClick event creates a range called rng like this

Set rng = Union(Range("RangeName1"), Range("RangeName2"))

and so if there were a RangeName3 or 4, etc. the double-click would not be recognized. If you need help fixing that then let me know via another question. the same code also password protects the sheet but I can temporarily take care of that.
28814580.xlsm
0
Bright01Author Commented:
Great progress!  Yes.  That works very well!  So you load up the % and the Macro converts it to dollars when the time comes to show value.

Excellent.

B.
0
Martin LissOlder than dirtCommented:
OK so I'll get on with the rest. BTW you may have missed my comment about the DoubleClick event that I added to my previous post.
0
Martin LissOlder than dirtCommented:
Here's a version with totals. It seems to work fine when all the rows are checked,  but there's a problem with the formulas in cells E14, E15 and E16 if for example cell A18 is unchecked. Test it first with all rows checked and then with A18 unchecked and tell me what you want done about it.
28814580a.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bright01Author Commented:
Getting there.  If you don't check the cell in A, there should be no calculation of dollar value.  

Does that make sense?

B.
0
Martin LissOlder than dirtCommented:
Are you saying that if for example that if cells A16,17,19 and 20 are checked and A18 is not checked that the total should be blank?
0
Bright01Author Commented:
So... for clarification, what you have now is great.  Converting all of the % to $ in the table shows what is available.  IF HOWEVER, you select a row via a check mark, it then is calculated (added/summed) in row 12.  In other words, you are choosing to apply the math only to a row that is selected in the total for that table/column.

EXAMPLE:  If I uncheck row 9, then the total in E12 would be $80,000, not $100,000.  And so on in F12 and G12.

Make sense?

B.
0
Martin LissOlder than dirtCommented:
I think that before we do anything more here that you should close this question and start a new one. But before you do that,

1.    Did you see the problem I described in post 41201184?
2.    In post 41204246 you say  "IF HOWEVER, you select a row via a check mark, it then is calculated (added/summed) in row 12". Are you aware that in the workbook you posted it doesn't do that now?
0
Bright01Author Commented:
Martin, Thank you for taking this to the next level.  I know this was a complex request and I made several errors in trying to describe what I was looking for.  I will post another request after I test it and can be very specific as to how it should behave.

Thank you very much and I hope you will pick up the next post.

All the best,

B.
0
Bright01Author Commented:
Martin,  I think I discovered the confusion and hopefully corrected it.  The original request had text in the example that I had to change as we got into how this was going to work.  I never went back and changed the text/request.  So I have now authored another question to fix the issue.  

Here is what I realize.  There are really 2 macros that need to be in this.  One is fired by the Button (you created this Macro) and it should change ALL of the values in the Table from % to $ and back.  No selection (check mark) should influence this.  Simple.

The second Macro should look at the check mark selection and if made (i.e. a "P" is selected by double clicking) then it simply adds the $ value, in that row, from the Table * the Question Value, in cells E12, F12 and G12.

This accomplishes what I was after.  Convert the % to $ and back/forth.  And total only the selected amounts.

I hope you will pick up the question.  Others are going to have a tough time figuring this out.

Happy Saturday!

B.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

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.