Tech Support Guy banner
Status
Not open for further replies.
1 - 10 of 10 Posts

·
Registered
Joined
·
3 Posts
Discussion Starter · #1 ·
I have set up table in Excel where employees are listed down the side, and SOP's are listed across the top, and I enter a 1 in every cross referenced square to indicate they have been trained on each SOP. What I would like is to have a sheet that reads this data and then allows me to sort by name what SOP's each employee has been trained on, AND be able to alternately sort by SOP and be given a list of employees trained. In addition, I would like a way to be told what SOP's each employee REMAINS to be trained on.

I understand the IF function but don't know how to do like a mass check instead of a single cell check.

Any help is greatly appreciated. In addition, if you feel I could better be served using Access, feel free to explain how I might go about that...I've never used Access before.

Thanks!
 

·
Registered
Joined
·
5,458 Posts
Hi,

How many SOP's do you have, and how many names do you have? You should be able to use Excel's native sort function from what it sounds like. You can attach a sample file to the board also (Manage Attachments button when posting/replying).
 

·
Registered
Joined
·
98 Posts
Seems pretty simple. You could do Access, but that's much more complicated to build.
I did a samle with 20 employees and 10 SOP's accross the top. With the columns pretty narrow, this fits nicely on a page. I did a conditional format of the cells so that they would turn a bright color with an outline border when you enter "1" in the cell. Then I made a total column at the right to count the cells with data [=COUNT(B2:K2)]and another column to count the blank cells [=COUNTBLANK(B2:K2)]. This will give you a count of SOP's taken, and SOP's remaining. You can easily sort, or just visually see which are needed for each employee.

I'm sure you could take it further make a pivot table to create a table of SOP needed or SOP taken. You may have to enter the SOP name in each cell down the column, and replace it with a "1" when taken or something, so that the cells are not blank. I'd have to play with that one.
 

·
Registered
Joined
·
98 Posts
Actually, I just played with filters, and that could work kind of slick perhaps.
If you set filters, you can "hide/display" blanks (those who have not had the SOP) or "1's" (those who have). You would probably do this for one SOP at a time.
 

·
Registered
Joined
·
3 Posts
Discussion Starter · #5 ·
I have a working copy which I am going to attach here. HOWEVER the reason I am doing so is this working copy is only "version 1.0" if you will...I still need alot of advice on how to accomplish this without all the code entry I have in this file.

If you will: look at the "Data Grid" tab. This is the user interface. Very simple...a cross reference grid with 1 in the cells under each SOP name representing TRAINING COMPLETE. Please add some 1's and look at the "By SOP" and "By Name" tabs to see the results I needed. Each of those tabs have drop down filters and are EXACTLY the result I needed!

BUT NOW!!!!!.....

UNHIDE everything after you look at the functionality of this file! After you unhide (all the hidden stuff is in the "Data Grid" sheet) you will see the absolute nightmare of a time I had getting what seemed like a simple task accomplished! These other tables took me about 4 days to enter and edit even after I utilized a keyboard macro editor to fill in alot of cells. ALSO NOTE that despite knowing about the ctrl+enter trick to enter multiple formulas into cells, that the trick only gies so far as some formulas only need incremented on ONE part and NOT the other, and by default ctrl+enter increments BOTH.

So a quick explanation of all those tables:

1 table 23X100 (same size as user interface) to read the title of each SOP completed PER NAME

1 table the same only for each uncompleted

THEN (since a function is limited to so many arguments)

2 tables to read all the completed names and bunch them together as a string of text - and 2 more for uncompleted...these are on 2 each ALSO due to a 15 limit on the CONCATENATE function (but 23 names!!!)

THEN....

Repeat all of the above for the table to sort by SOP instead of Name!!!! - this set of tables was 1 for positive, 1 for negative, 7 for massing positives and 7 for massing negatives (remember...100 SOP's but only 15 can be put in CONCATENATE at a time!)

SOOOOO


Have a look and then PLEASE make me look STUPID with a reply of "Why didn't you use the arrays?" or something (I know they exhist, but couldn't figure how to make it work right!) AND PLEASE PLEASE PLEASE someone chime in on ACCESS as well for this project since I am slated on a project 35 TIMES AS BIG as this one due in January, and somehow I just KNOW I should be using ACCESS instead.


THANKS!!!
 

Attachments

·
Registered
Joined
·
98 Posts
Wow. Luni you are very creative. Your tables are wonderful, and I'm not sure that I can help you any more than that. I agree, there must be a simpler way. I tend to go overboard myself, and then a year later look back and slam my head into the wall saying "Geez! Why didn't I just ........"

I haven't looked at Dreamboat's suggestions, but I'm not sure what you mean by user interface? Are you downloading or uploading data?

I'll try to browse the file some more if I can find some spare time. Maybe something will come to me in the middle of the night.

J.
 

·
Registered
Joined
·
11,755 Posts
What doesn't work?

If you're talking about your two different layouts on the other sheets, you might want to consider a mail merge to Word. Then you can lay it out however you like.
 

·
Registered
Joined
·
5,458 Posts
I've uploaded a file with a UserForm example on it. Code is posted below (UserForm would need to be created with the same objects as in the example, or just use the example form) ...

Place in a Standard Module:

Code:
'** Code originally developed by Zack Barresse
'**   on October 14th, 2004
'** Declaring all public messages for workbook
Public Const DblSpc As String = vbNewLine & vbNewLine
Public Const NameMsg As String = "Name is not found!" & DblSpc & _
    "Please check your name list and try again."
Public Const SOPMsg As String = "SOP not found!" & DblSpc & _
    "Please check your SOP list and try again."
Public Const Thanks As String = "Thanks for supporting VBAX!" & DblSpc & _
    "© 2004, VBAExpress.com"

Public Sub RefreshList()
'** Refreshes ListBox with SOP's
    Dim cel As Range, rng As Range
    Set rng = Sheet3.Range("B1:CW1")
    With UserForm1.ListBox1
        .Clear
        For Each cel In rng
            .AddItem (cel.Value)
        Next cel
    End With
End Sub

Public Sub RefreshCombo()
'** Refreshes ComboBox with Names
    Dim cel As Range, rng As Range
    Set rng = Sheet3.Range("A2:A24")
    With UserForm1.ComboBox1
        .Clear
        For Each cel In rng
            .AddItem (cel.Value)
        Next cel
    End With
End Sub

Sub callMe()
'** Loads the form
    Load UserForm1
    UserForm1.Show
End Sub
Place in the UserForm Module:

Code:
'** Keep Sub's here Private to dictate only this UserForm
'**   allowing for expansion of other Forms
Option Explicit

Private Sub CheckBox1_Click()
'** Select All/Deselect All
    Dim i As Long
    With Me.ListBox1
        Select Case CheckBox1.Value
        Case True
            For i = 0 To .ListCount - 1 Step 1
                .Selected(i) = True
            Next i
        Case False
            RefreshList
        End Select
    End With
End Sub

Private Sub CommandButton1_Click()
'** ADD TRAINING Button
    Dim i As Long, n As Long, endList As Long, tmp As Range, rng As Range, myRow As Long
    With Sheet3
        Set rng = .Range("A2:A24")
        With rng
            Set tmp = .Find(ComboBox1.Value, lookat:=xlWhole, MatchCase:=True)
            If Not tmp Is Nothing Then
                myRow = tmp.Row - 1
                For i = 0 To ListBox1.ListCount - 1 Step 1
                    If ListBox1.Selected(i) Then
                        .Cells(myRow, i + 2).Value = 1
                    End If
                Next i
            Else
                MsgBox NameMsg
                Exit Sub
            End If
        End With
    End With
    RefreshList
    RefreshCombo
    If Me.ComboBox1.Value > 0 Then Me.ComboBox1.ListIndex = 0
End Sub

Private Sub CommandButton3_Click()
'** REMOVE TRAINING Button
    Dim i As Long, n As Long, endList As Long, tmp As Range, rng As Range, myRow As Long
    With Sheet3
        Set rng = .Range("A2:A24")
        With rng
            Set tmp = .Find(ComboBox1.Value, lookat:=xlWhole, MatchCase:=True)
            If Not tmp Is Nothing Then
                myRow = tmp.Row - 1
                For i = 0 To ListBox1.ListCount - 1 Step 1
                    If ListBox1.Selected(i) Then
                        .Cells(myRow, i + 2).Value = ""
                    End If
                Next i
            Else
                MsgBox SOPMsg
                Exit Sub
            End If
        End With
    End With
    RefreshList
    RefreshCombo
    If Me.ComboBox1.Value > 0 Then Me.ComboBox1.ListIndex = 0
End Sub

Private Sub CommandButton2_Click()
'** CANCEL Button
    Unload UserForm1
End Sub

Private Sub UserForm_Initialize()
'** Start with fresh Lists
    RefreshList
    RefreshCombo
    If Me.ComboBox1.Value > 0 Then Me.ComboBox1.ListIndex = 0
End Sub

Private Sub UserForm_Terminate()
'** Exit Message
    MsgBox Thanks
End Sub
HTH
 

Attachments

1 - 10 of 10 Posts
Status
Not open for further replies.
Top