Excel Newbie

[AG9292]

Hello,

I am new to understanding the benefits of utilising excel and the wealth of features it has, so I have a very basic understanding of formulas etc.

I am a Social Work Team Manager in Scotland. As part of my role, I need to get keep track of individuals who have been deemed to lack capacity in relation to their welfare and finances. After an individual has been deemed incapable, there is a process where either the Local Authority or a Private Applicant applies for a Guardianship Order, similar to a Conservatorship in the States.

There is a total of 10 steps in this process; this is the same steps for either a Local Authority Order or a Private Order.

I would like an excel spreadsheet that has a summarised dashboard that is easy on the eye and easy to understand. However, there also needs to be a more comprehensive page with all of the data for each of the applications including dates and each stage was completed. However, it would be good to have ‘triggers’ that alert the owner of the spreadsheet that an update is required. It would also be really helpful if there was a communication log/ update log for each of the applications.

I hope this makes sense – basically it needs to be a tracker with the ability to check a step/ task as complete or incomplete and there can be two different types of application and also a communication log would be beneficial.

If there was a way to collate data from the spreadsheet to advise of the timescales of each step for reporting purposes, that would also be helpful.

Is there anywhere you’d suggest I should look for tutorials either paid or free to help me with this, if it can even be done. It’s excel 2016 I have in work - windows.

I have a MacBook with excel too, unsure of the year of it.

Thanks,
AG9292

 

[Logit]

We can work on this project together, in Windows.

Are you familiar enough with Excel that you can cobble together an example workbook (non-functional of course) which will present how you envision
the workbook will look ? Perhaps you are already utilizing paper forms for your processes and we can possibly use those as a starting point ?

Let me know.

 

[AG9292]

We can work on this project together, in Windows.

Are you familiar enough with Excel that you can cobble together an example workbook (non-functional of course) which will present how you envision
the workbook will look ? Perhaps you are already utilizing paper forms for your processes and we can possibly use those as a starting point ?

Let me know.

Thank you for coming back to me.

I can pull something together on excel. Is there anything you need me to include specifically?

 

[AG9292]

 

[Logit]

Describe in better detail what the image represents. There are two types of orders and eighteen steps.

Does the first order relate to the first nine steps and the second order relate to the second nine steps ?
Should this information be included on a sheet with the patient's name ? Should each patient have their own sheet ?
Is there additional information related to the patient that should be included on the sheet ?

What about a time stamp to indicate when a step was completed ... is that required ?

 

[Umma123]

This sounds like a big task that Excel could handle better with some customization. To set up a tracker for each step with automatic alerts, you may find yourself using conditional formatting combined with formulas like IF or TODAY to flag in past tasks Also, summarizing progress in applications using Excel's Reporting deadlines with PivotTables can be easy.

 

[AG9292]

Describe in better detail what the image represents. There are two types of orders and eighteen steps.

Does the first order relate to the first nine steps and the second order relate to the second nine steps ?
Should this information be included on a sheet with the patient's name ? Should each patient have their own sheet ?
Is there additional information related to the patient that should be included on the sheet ?

What about a time stamp to indicate when a step was completed ... is that required ?

So the Local Authority Order is the second set of steps, and the Private Order is the first set of steps. The steps are the same, except in the Private Order, there is a process for applying for Legal Aid.

The information should be aligned to a patient, I haven't really thought about whether each patient should have their own page or whether it should be all on one data page. Although, an individual page, may allow for better communication/ updates to be logged.

A time stamp would be helpful as well, as it would be good to be able to determine how long it has taken from the start of the process and how long it took between each stage to complete said process.

 

[AG9292]

This sounds like a big task that Excel could handle better with some customization. To set up a tracker for each step with automatic alerts, you may find yourself using conditional formatting combined with formulas like IF or TODAY to flag in past tasks Also, summarizing progress in applications using Excel's Reporting deadlines with PivotTables can be easy.

This sounds like really good ideas - are you aware of any tutorials I can access online either free of charge or pay for that would teach me more about conditional formatting and the formulas you mentioned?

I am able to do pivot tables, but basic things only.

 

[Logit]

Here is part of the code in the workbook :

Option Explicit

Sub CopySheet()
 
    Dim Visible     As XlSheetVisibility
    Dim s As String
    Dim r As String
    
    Application.ScreenUpdating = False      ' hide the action from view
    
    s = InputBox("Enter LAST NAME")
    r = InputBox("Enter FIRST NAME")
    With Sheet1
        Visible = .Visible                  ' record the sheet's visibility setting
        .Visible = xlSheetVisible           ' make the sheet visible
        .Copy After:=Sheets(Sheets.Count)   ' create a copy (the copy will be ActiveSheet)
        .Visible = Visible                  ' reset the sheet's Visible property to what it was before
         With ActiveSheet
            .Name = s & ", " & r
            .Range("B2").Value = s
            .Range("D2").Value = r
            .Range("G2").Value = Date
        End With
    End With
    Application.ScreenUpdating = True
End Sub

Download workbook : Dropbox

 

[Logit]

Oh ... if you double click a cell in the Date Completed column, the date is automatically inserted.