Coffeehouse Thread

3 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

Excel Question

Back to Forum: Coffeehouse
  • User profile image
    Manip

    Example Data:

    Title X Y  Z
    Joe 10 9 10
    Pam 4 11 8
    Tim  3 94 1


    In Excel it is easy enough to draw a pie chart for one row, that would include a graph representing X + Y + Z and to set the title source to the first column...

    But what I want to do is somehow make it user-selectable, so Excel would only ever be drawing one pie chart, but that chart can be on Joe, Pam or Tim's rows (columns never change)...
     
    It would be easy if I wanted to do it on *ALL* data or if I wanted to draw one graph for each one at the same time but I want to draw only one graph total and have the user select which row. Does anyone know a good method within Excel to do what I'm asking?


    Note: The real data contains well over five hundred rows so drawing each as a graph isn't an option.

  • User profile image
    DCMonkey

    Here's one way:

    Add a row between your title row and first data row (you can put it elsewhere later but my example will put it there). This would make the new row A2:D2

    Using the Data->Validation menu item, set the data validation "allow" to List and set the list's range to the list of names in your main data table.

    In B2 use the vlookup function to lookup the current X value in your main table using the value selected in A2 as your lookup value like so:
    =VLOOKUP($A$2,$A$4:$D$6,2)

    Do the same for the other columns. Be sure to increment the column index number for each column's function.

    Base your pie chart on the row of vlookup formulas. Use the drop down in cell A2 to select a the name you want the pie chart to display.

  • User profile image
    SimonJ

    Select Row 1 (Title etc) then Ctrl+Click to select row 3 (Pam etc)
    Now invoke the Chart Wizard, select Pie chart and click Finish.

    The Chart Wizard correctly interprets the first row as headings and the other row as the data.

    If you want to do it in code, you just need some method of indicating which row to graph. I would suggest a combo box showing all the names in column A. You could put this on the sheet or on a userform.

    SimonJ

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.