Better way to add data for generating graph by person

I need a better way for staff to enter data in order to generate a graphs. Preferably, the graph display would be on a single page, and you would simply select the person with a radio or list button and the graph would display automagically.
details: In the sheet 'Graph Data Entry' I have four staff who need to enter the same data. I would like to:
Allow data entry easier for the staff w/o scrolling around, but this is just a nicety.
I want to be able to display a single person's graph on sheet 'Team Dashboard WIP', by just selecting the person with list, combo, or radio control.
dashboard-wip.xlsm
singleton2787Asked:
Who is Participating?
 
SiddharthRoutConnect With a Mentor Commented:
Is this what you want?

Sample File Attached.

Sid

Code Used

Private Sub Worksheet_change(ByVal Target As Range)
    If Not Intersect(Target, Range("E5")) Is Nothing Then
        Dim acell As Range
        
        Set acell = Sheets("Graph Data Entry").Columns(1).Find(What:=Range("E5").Value, LookIn:=xlValues, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
    
        If Not acell Is Nothing Then
            Range("E9").Value = acell.Offset(2, 2).Value
            Range("E10").Value = acell.Offset(4, 2).Value
            Range("E11").Value = acell.Offset(6, 2).Value
            Range("F9").Value = acell.Offset(2, 3).Value
            Range("F10").Value = acell.Offset(4, 3).Value
            Range("F11").Value = acell.Offset(6, 3).Value
            Range("G9").Value = acell.Offset(2, 4).Value
            Range("G10").Value = acell.Offset(4, 4).Value
            Range("G11").Value = acell.Offset(6, 4).Value
            Range("H9").Value = acell.Offset(2, 5).Value
            Range("H10").Value = acell.Offset(4, 5).Value
            Range("H11").Value = acell.Offset(6, 5).Value
        End If
    
    End If
End Sub

Open in new window

Dashboard-wip.xlsm
0
 
Rory ArchibaldCommented:
See attached - I've added a data validation list for the list of people and some formulas above the chart (you can simply position the chart over the formulas to hide them).

Reegards,
Rory
dashboard-wip.xlsm
0
 
SiddharthRoutCommented:
Nice one Rorya, using formulas. Never thought of that...

Sid
0
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
singleton2787Author Commented:
Nice...but when I change the data in the 'Graph Data Entry' sheet, the graph doesn't update anymore. Do I have to refresh it somehow? Sorry for the possible noob question...
0
 
SiddharthRoutCommented:
The data is the same for every one in the "Graph Data Entry" and that is why it appears that it is not updating :)

Sid
0
 
Rory ArchibaldCommented:
Not sure who you are talking to? (just tested mine and it does work for me)
0
 
SiddharthRoutCommented:
singleton2787: In my file, if you will move the chart, you will see the data behind it, That is where the data gets updated. I think I might not have connected the chart to that data but I am sure that you can manage that.

Sid
0
 
singleton2787Author Commented:
Oh, also...when I copied the Sheet to another workbook it thew a VB error:
Ambigious name detected: Worksheet Changed

Is there a way to fix this?
0
 
SiddharthRoutCommented:
Yes. Can you upload your file? I will change it for you.

Sid
0
 
singleton2787Author Commented:
I changed the data behind the graph...maybe I am just fumble fingering it?
dashboard-wip.xlsm
0
 
SiddharthRoutCommented:
That is becuase you posted the code that is meant for Sheet "Team Dashboard WIP" in Sheet "New Layouts" :) And there you have 2 worksheet chnage events.

What do you want to exactly do in the Sheet "New Layouts"?

Sid
0
 
SiddharthRoutCommented:
Do you want to connect it to sheet "Data" ?

Sid
0
 
singleton2787Author Commented:
I figured it out, sorry. Just relinked the graph data like you said.
0
 
SiddharthRoutCommented:
singleton2787: Both our solutions (mine and rorya's work for you. So it is just unfair if you allocate the entire points to me. It should have been shared with Rorya as well :)

Sid
0
All Courses

From novice to tech pro — start learning today.