Solved

Priority Graphic Selection and Reset

Posted on 2012-03-13
24
259 Views
Last Modified: 2012-03-15
EE Professionals,

Some time ago, a very nice graphic was rendered (Teylyn) and I have been waiting to use it.  Now I'm working to refine it and am having some challenges.  Here's what I'm looking for.

1.) The balls should be selected from a validation list (cell B2) once selected, they should appear in a "active" list (below cell B16).
2.) The user should be able to then prioritize any of the selected active choices through some sort of click or button selection and the use of the sliders on each particular selection.
3.) A button to reset or clear the selection to start over.

That's it!  And "thank you" in advance.

B.
SliderGraphmultiplevars-r7.xlsm
0
Comment
Question by:Bright01
  • 14
  • 10
24 Comments
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
Hello Bright,

run that past me again:

>>1.) The balls should be selected from a validation list (cell B2) once selected, they should appear in a "active" list (below cell B16).

So, user selects a value in B2. That value should be copied to B16. So far so good.

User selects another value in B2. That value should be
- copied to B17, i.e. the next empty row below B16 ??
- something else?

User keeps selecting values, and they all end up in one list starting from B16 until user selects "Reset All"? User may not select a value more than once?

>>2.) The user should be able to then prioritize any of the selected active choices through some sort of click or button selection

What will that prioritization accomplish? Is it about the order of the data series in the chart?

>>... and the use of the sliders on each particular selection.

I assume the sliders will be used to set the X, Y and bubble size values for the chart? Where do you want to store that data? In the table starting in column H? Or do you want to start another table for the chart data?

We'll need to break this up into chunks, because there are a few complexities involved.

So, let's start with 1.) and where you want the chart data to go. Once we have that in place, we can tackle 2.)

Using conventional formulas, it is possible to reduce the drop-down list by the elements that have already been selected, so they can't be selected again. Combined with a Worksheet_Change event, each selected element can be placed in B16 and following, until a reset command is issued.

I'd hate to muck around with the existing code before we have defined the parameters, though.

Re 2.), prioritizing could happen with a helper column where the user enters the numbers 1-xx against each item in B16 and below, clicks a button and the whole sheebang will be sorted according to the numbering. (yes, it's possible to get more fancy, but it would involve a lot more buttons for up/down and a lot more macro code).

Re 2a.) collecting the X, Y and Z for each item in B16 and below would be another procedure, which I have not quite figured out yet, since it also depends a bit on the clarifications re 1.)

Looking forward to your reply.

cheers, teylyn
0
 

Author Comment

by:Bright01
Comment Utility
>>1.) The balls should be selected from a validation list (cell B2) once selected, they should appear in a "active" list (below cell B16).

YES ( I am open to other ways to "select" from the validation list...this is but one.....and what I like about it is that you probably won't select more then 6)

User selects another value in B2. That value should be copied to B17, i.e. the next empty row below B16 ??

YES

User keeps selecting values, and they all end up in one list starting from B16 until user selects "Reset All"? User may not select a value more than once?

No.  A user can select as many values as they want but may elect to select some subset of the entire list.

YES.  A user MAY NOT select a value more then once.

>>2.) The user should be able to then prioritize any of the selected active choices through some sort of click or button selection

I SHOULD BE MORE CLEAR ON THIS POINT. By selecting a particular choice it appears on the list (below B16).  Since you only have one set of sliders, you must specify which choice you are operating on.  I do not know what visual should be used if any but you have to have a way to use the sliders against the choice.

I assume the sliders will be used to set the X, Y and bubble size values for the chart? Where do you want to store that data? In the table starting in column H? Or do you want to start another table for the chart data?

I had two thoughts on this.   1.) The data could be stored anywhere on the WS because I'm only going to show the graphic and the list.  It seems smart to always have the data and the graphic on the same WS in the event you want to use it again in another WB later.  or 2.) The data could be stored in the Calc_Engine where it really belongs.  YOUR CHOICE.

Thank you for asking questions to clarify.  Sometimes these complex charts require itteration but in all cases it's best to flush out the design before going at it.

B.
0
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
OK, got it.

Next: the current chart source table has a whole ballpark of things going on re High/Low and bubble color in columns K, L and M.  I haven't quite figured out if that really is reflected in the actual bubble colors or not. If so, do you want to retain that functionality? In that case, it would make sense to store (or relate) the chart source to those columns and leverage off the existing mechanisms (if any).

In any case, I'll need to catch some ZZZzzzz for the next few hours and will have a look at this tomorrow (NZ time)

cheers, teylyn
0
 

Author Comment

by:Bright01
Comment Utility
Teylyn,

I know there are many formulas in the model to the right of the graphic.  I think they were originally there because I had a static list that was being selected and the formulas helped drive the graphic.  You are welcome to do what you want with "the ballpark"; clean and simple wins the day!

Thank you,

B.
0
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
Bright,

have a play with the attached.

Click Reset All to start with a blank slate.

Select outcomes from the yellow drop down and see them appear in B16 and below.
Note that once an outcome appears in B16 and below, it is no longer available in the drop-down.

Select any populated cell in B16 and below and use the sliders to change the values.
Use the "Set values" button to commit the changes to the chart.

Every time "Set Values" is clicked, the macro to color the bubbles will fire, leveraging off the formulas you already had in place.

When switching selection between cells B16 and below, the sliders will reflect the current values for the selected outcome. When clicking somewhere else, the sliders will show the values for the most recently selected outcome and will list that in B12.

You can always manually change the values in the chart source table in columns H to J. When the outcome is next selected in column B, the sliders will reflect these changes.

I added a new standard module and added code to the Priorities sheet module. I've commented out code that got in my way, but I did not delete anything.

cheers, teylyn
SliderGraphmultiplevars-r7-teyly.xlsm
0
 

Author Comment

by:Bright01
Comment Utility
Teylyn,

This is excellent!  I tested it and it works very well for what I'm trying to accomplish.  Just one or three minor tweeks;

Is there a way where the cursor and active outcome you are modifying will stay on THAT OUTCOME until you move the cursor?  Right now you have to reposition it each time you "fire" the new values.  

Size of Investment needs to read opposite of what it reads now.  In other words, the larger the investment, the smaller the size of the ball (less attractive if it costs more).

How do I get the Outcome Name to display either inside or beside each ball?


Many "thanks"....... this is very good work!

B.
0
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
Hi again,

see attached.

>>Is there a way where the cursor and active outcome you are modifying will stay on THAT OUTCOME until you move the cursor?

Yes. After clicking the "Set Values" button, the cell focus will now return to the previously selected outcome cell.

>>Size of Investment needs to read opposite of what it reads now.

The slider value will now be reversed when copied to the chart source table, i.e. a slider value of 90 will be 10 in the table (small bubble size) and a table value of 80 (large bubble size) will become 20 in the slider when the outcome cell is selected, calculated with the formula 100 - valueX

>>How do I get the Outcome Name to display either inside or beside each ball?

I added the series name as a data label to each bubble. For ease of reference, it is centered on the bubble, otherwise, it may not immediately be clear what bubble it belongs to.

Note: I'm at home now, working in some obscure version of Excel that may (or may not) produce the same results in Excel 2010. If you find something amiss, please pipe up and I'll fire up the other laptop.

cheers, teylyn
SliderGraphmultiplevars-r7-teyly.xlsm
0
 

Author Comment

by:Bright01
Comment Utility
Teylyn,

I have racked my brain for 3 hours on integrating the table & Graphic into my master WS, and I need to see if you could take a peek at it to see where I have failed.  I think I have narrowed down the problem.

1.) I have checked all Range names and have matched them  up; item for item.
2.) I have copied over the two tabs and insured they are the same (Calc_Engine is now CA_Calc_Engine and Priorities didn't change)
3.) I have copied over the two modules (Now Module 10 and 11) and made the slight adjustments to reference the correct WSs for the slider code.
4.) The Reset Macro should probably have a WS reference.

I have mocked up the integrated WB for you to take a quick look at.  The "table" in the calculations section begins in O113 (CA_CALC_Engine).

I think I've seen this problem before.  When you select the "Available" name range, I get a different response to what you have although the formulas are exactly the same!  And it is in this column where, while the formulas match up; I'm not getting any of the input data/items that would display in the DV Box on the Priorities tab.  Here is the formula where my efforts went "dead".

=IFERROR(INDEX(Original,SMALL(unused,ROW(O113))),0)

Please take a look..... honestly, I wouldn't ask you if I hadn't spent the past 3 hours trying to trouble shoot the move over from your code to the master WB.

Thank you,

B.
Copy-of-Graphic-Problem-with-Wor.xlsm
0
 

Author Comment

by:Bright01
Comment Utility
As I work this back through; it either has something to do with the range name "Available" -- however when I go back to the column representing the defined "range name" it is blank so I"m back to the formula above not populating correctly.

B.
0
 
LVL 50

Accepted Solution

by:
teylyn earned 500 total points
Comment Utility
Hi,

I see how this can be confusing. Some formulas need the "row(A1)" as a cell reference, because they need to return the number "1". In my first example, the whole setup started at A1, so it was easy to make the (wrong) assumption that A1 changes to Q113 when the setup is moved down and right in the sheet.

In P144 put

=IF(COUNTIF(Priorities!$B$16:$B$40,CA_Calc_Engine!O114)>0,"",ROW(A1))

copy down. This formula will number the unused data items from 1 through 14.

In Q114 you need

=IFERROR(INDEX(Original,SMALL(unused,ROW(A1))),0)

This small() function will return the nth smallest value, where Row(A1) provides the n -- so in row 114 it's the first, and after that the 2nd, 3rd, etc. That's why the row function uses A1 and not Q113. (you could use Q1, of course, because we're only interested in the row number, not the column)

see attached.

cheers, teylyn
Copy-of-Graphic-Problem-with-Wor.xlsm
0
 

Author Comment

by:Bright01
Comment Utility
Teylyn,

You are amazing!  However, I'm still doing something incredibly stupid here.  I copied in the formula and it now is showing me the formula instead of calculating the result.
Something-Wrong.png
0
 

Author Comment

by:Bright01
Comment Utility
Teylyn,

After toubleshooting; got it!

Thank you!

B.
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Closing Comment

by:Bright01
Comment Utility
You really are the best with graphics and formulas........... much thanks; if I could give you more then 500 points, I would!

B.
0
 

Author Comment

by:Bright01
Comment Utility
Teylyn,

When I select a Outcome, and then hit the value button, I get an error on this line:

   .Interior.Color = Range(Range("BallColor_Addresses").Cells(1, 1).Offset(myInitiativeCounter, 0)).Interior.Color

I get it on your copy as well.  Can you take a quick look?

B.
0
 

Author Comment

by:Bright01
Comment Utility
Wait.... I'm making progress!!!!
0
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
Sorry for my silence. I was offline for the last few hours. How are you getting on ?
0
 

Author Comment

by:Bright01
Comment Utility
No worries....been having fun with the graphic.  

Specifically, if I want to change the Cost (3rd slider) back to -0- (not 100) on the slider, however, have it behave in a manner that the higher the number, the smaller the ball, how exactly do I do that?

B.
0
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
The slider can only have a small number as a minimum and a larger number as a maximum value. No way around that.

In order to invert the value, you need to involve some calculation in the macro that writes the slider value into the target table cell, and again in the macro that reads the table cell into the slider linked cell when the outcome is selected.

With a slider value from 0 - 100 the formula is easy, i.e. 100 - sliderValue (see the macros in the attachment to post  37721871.

1.) To write the slider value to the cell I used this code in Module 11 in your attachment:
Sub SetValues()
Dim cel As Range
Dim lRow As Integer

[ .. several lines of code in between ]

    Cells(lRow, "J") = 100 - [SizeInv_Slider].Value

[ .. several lines of code in between ]
end sub

Open in new window

2.) To read the table value back into the cell linked to the slider there is code in the Sheet module of the Priorities Sheet
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' When an outcome is selected, transfer the values from the chart source table
' to the slider fields

[ .. several lines of code in between ]

        [SizeInv_Slider].Value = 100 - Cells(lRow, "J")

[ .. several lines of code in between ]
end sub

Open in new window

If you assign different min/max values to the slider, that formula needs to be adjusted.
0
 

Author Comment

by:Bright01
Comment Utility
After testing this; there might be some slight error checking that needs to happen.  For example, if you move the first slider; then try to set values, you get a debug message on line:

  .Interior.Color = Range(Range("BallColor_Addresses").Cells(1, 1).Offset(myInitiativeCounter, 0)).Interior.Color

Additionally, if you don't move the 3rd slider on "Cost", you do not get a graphic..........

Those are two of the problems a user might have.

B.
0
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
Hmm.

Yes, I see that. Maybe the macro needs to check that the linked cells for the sliders are not empty or show a value of at least 1.

Give me a few minutes.

cheers, teylyn
0
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
Try this in Module 11 (replace the existing sub called SetValues)

Sub SetValues()
Dim cel As Range
Dim lRow As Integer

If Not TypeName(Selection) = "Range" Then
    MsgBox "Please click one of the selected outcomes before clicking 'Set values'!"
    Exit Sub
End If

If Selection.Row < 16 Or Selection.Row > 29 Then
    MsgBox "Please click one of the selected outcomes before clicking 'Set values'!"
    Exit Sub
End If
' only run this code when all three slider link cells have values
If WorksheetFunction.Count([Cmplx_Slider], [BVCost_Slider], [SizeInv_Slider]) <> 3 Then
    MsgBox "Please select a value for each of the three sliders."
    Exit Sub
End If
' find the row of the selected cells in the chart source table
    lRow = Selection.Row - 12
' transfer the slider values to the chart source table
    Cells(lRow, "H") = [Cmplx_Slider].Value
    Cells(lRow, "I") = [BVCost_Slider].Value
    Cells(lRow, "J") = 100 - [SizeInv_Slider].Value
    UpdateChartBallColors
    Range("B" & lRow + 12).Select
End Sub

Open in new window


I get some funny behaviour outside of this immediate scope, because some things that your file needs are not found in the places it expects them to, but the macro in question is not affected, I think.
0
 

Author Comment

by:Bright01
Comment Utility
That seems to work very well!  Where do I look for changing the Cost slider back to -0- instead of 100 at reset?

Thanks for sticking with this.  I'll stop asking you questions.  I am very pleased with this graphic!

B.
0
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
>> Where do I look for changing the Cost slider back to -0- instead of 100 at reset?

I can't answer that, since the "Reset All" button is linked to a macro that is not included in the file attached above. If you can post the code that  sits behind that button, I'd be happy to help.

Maybe this could be a new question, though, since it is not directly related to this charting issue, and you would get the chance to trial the expertise of other experts who won't go to bed in ten minutes.

cheers,
0
 

Author Comment

by:Bright01
Comment Utility
Will do!  Thank you.  And good night.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
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.

744 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

15 Experts available now in Live!

Get 1:1 Help Now