New Macro for Calculating Dollar Value against a Percent

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.

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!)

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

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?

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.

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?

Actually, neither are "Assets".  They are actually "Use Cases" and the table rows are "Sub-Use Cases".

Thanks for asking.

B.

Just a thought. Why have the button at all and instead include both the $ and % values in the table?

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.

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.

Yes.  Good catch.  The Helper cells won't be active.

Thanks,

B.

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

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.

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.

Getting there.  If you don't check the cell in A, there should be no calculation of dollar value.  

Does that make sense?

B.

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?

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.

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?

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.

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.