Advancing Macro for Selecting Specific Rows and converting it into Value

EE Pros,

I have this WS that Martin Liss has graciously helped me with and has done a great job in getting this to this level.  

What I need is a correction to the Macro that does the following.

Current State:  Currently you put in a % value in a % table (e.g. E8:G11, and defined as a RangeName).  Then you put in a value in the Answer to a Question (in this case C12).  You can then select (by double Clicking the A Column in the Black Boxes) a particular row of your choice to calculate a total value that is placed in E, F and G 12.  When you change your choice in the check boxes (column A), the values in E, F and G12 should automatically adjust.  When you fire the BUTTON macro button, all of the % values in the Table should change to Dollars.  Presently they are dependent on the checked boxes.  This should not be the case.  The conversion of % to $ in the table is independent of the selection and summary.

Request:  I need it so that when firing the BUTTON macro, all of the boxes change in the table from % to Dollar and back.  HOWEVER, only the selected (checked) values are used to SUM the value at the bottom of each row in the table.  This should happen WITH A MACRO THAT LOOKS AT COLUMN A AND UPDATES THE SUMS IF THE ROW IS SELECTED.

I hope that is clear.  File attached for you to see.

Thank you in advance.

B.
D--Data-Data-Temp-Macro-Fix-from-28.xlsm
Bright01Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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:
Let me see what I can do.
Martin LissOlder than dirtCommented:
Do me a favor. Uncheck cells A16 and A18 and then press the macro button twice. Do you see what happens to E14:E16? What do you want to do about that?
Bright01Author Commented:
Martin,

The second Group of rows (16:20) should work the same way as the first in the table routine and the summary from checked row selection ( in Black Cells in Col. A).

Make sense?  

B.
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

Bright01Author Commented:
E14:G14, should behave just like what you have above in E6:G6.  The Totals are at the bottom of each table column and don't need to be repeated.

Hope that helps.

B.
Martin LissOlder than dirtCommented:
I don't think you understand what I'm asking.
???
What do you want to do about the circled values?
Bright01Author Commented:
Oh.... I see.  The values in the circles should simply represent the sum of the values from the table (both % and Dollar) and should not change at all based on Check Mark Selection.  It only toggles between $ and % views.  So if I have 5 values in cells E16:E20, say 3%, 2%, 1%, 1% and 1%..... Cell E14 (merged cell) would register 8%.  And with $200,000 in cell C21, the value when I push the button, would show $16,000 (or $200,000 X 8%) or (The Sum of cells E16:E20).    The only value that changes when I check a box in Col. A is in the value sum at the bottom of the column ( in your example above, this would be cells E21:G21).

Any clearer?

B.
Martin LissOlder than dirtCommented:
That means I need to change the formulas because right now they simply add up the values below them.
Bright01Author Commented:
So....just to be clear.  The value at the top of the table (% and $) is the sum of the values below in the table.  if you change the %  in the table, the value at the top changes.  But has nothing to do with selection (check marks).  With the check mark selections, the value at the bottom of the table (% and $) changes based on the selections and is only in  $ Value and the some of ONLY the "selected" rows. This value changes each time a selection (checkmark) is made.

B.
Martin LissOlder than dirtCommented:
What should the value be In the yellow cell in the circle?
Bright01Author Commented:
The value in the Yellow Circle E14 (the "Conservative" Column) should be the sum of the values in rows 16 through 20...... with the ability to translate the % into $ when you press the button.  The only time the number in the Yellow Circle will change is if I change the values in 16 through 20.  Then it should be reflected in the Yellow Circle.   If I select a row with the check marks, then only the sum number at the bottom changes (as it is the sum of the SELECTED values that make up the number in cell E21.

Make sense?

B.
Martin LissOlder than dirtCommented:
OK then that means I have to change the current formula which is =SUM(E16:E20) to something else (or calculate it) because with that formula it's adding the 4 cells below it and coming up with 12000.04.
Bright01Author Commented:
Well, if it is represented as a $ amount it might be something like $70000 representing a 70% sum of (E16:E20).   But when you fire the Macro, it will show one, say in % (i.e. 70%), and fire it again, and it will show the value in $  (i.e. $70,000).
Martin LissOlder than dirtCommented:
I think I have it now but before I post my current wb, I have another question that has nothing to do with the macro button. Should I immediately update the lower total (for example E12) when, say, A8 changes from checked to not checked?
Bright01Author Commented:
"YES"!  When A 8 changes, (i.e. the row is selected), the total in the Sum at the bottom of the %/$ Table should update.  This shows that a selection has resulted in more value and a higher % of the assumed value declared.  

B.
Martin LissOlder than dirtCommented:
Actually this was tough so if you don't mind can you post a new question for the immediate update? Here's what I have without that part. Check this out first before posting the new question please.
28819481.xlsm

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:
Martin,  This has been very interesting working with you on this because this is a tough case.  It almost works, but I'll be happy to close this out and post another question.  

Just so you will know, here is the issue.  Right now, if you make a selection in A, then there is a trigger on the table values.  

The way it should work is that the Macro that determines the addition to the cells at the bottom of the Table (i.e.  E12: G12) is the selection made to Column A.  Check off a row, and the value gets added to E12.  The other Macro, driven by the button, converts the $ to % and vice versa in the Table and the Top (SUM) row.

B.  

B.
Bright01Author Commented:
Martin, Thank you.  Tough project.  But when we get this finalized, it will be a very cool Excel App.

I'm very good with what you have done as progress.  I will post another question to further advance this macro.

Thanks again,

B.
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.