Solved

Priority Graphic Selection and Reset

Posted on 2012-03-13
24
289 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 14
  • 10
24 Comments
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 37713839
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
ID: 37713992
>>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:Ingeborg Hawighorst
ID: 37714047
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Bright01
ID: 37714459
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:Ingeborg Hawighorst
ID: 37717990
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
ID: 37718596
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:Ingeborg Hawighorst
ID: 37718684
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
ID: 37720578
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
ID: 37721206
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:
Ingeborg Hawighorst earned 500 total points
ID: 37721871
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
ID: 37722040
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
ID: 37722521
Teylyn,

After toubleshooting; got it!

Thank you!

B.
0
 

Author Closing Comment

by:Bright01
ID: 37722526
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
ID: 37722567
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
ID: 37722590
Wait.... I'm making progress!!!!
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 37723106
Sorry for my silence. I was offline for the last few hours. How are you getting on ?
0
 

Author Comment

by:Bright01
ID: 37723692
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:Ingeborg Hawighorst
ID: 37723723
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
ID: 37723732
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:Ingeborg Hawighorst
ID: 37723741
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:Ingeborg Hawighorst
ID: 37723762
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
ID: 37723958
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:Ingeborg Hawighorst
ID: 37723985
>> 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
ID: 37724031
Will do!  Thank you.  And good night.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

749 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