Project Planner Help

[PadaruthJ]

Hi

I'm new to this group, a friend recommended me to come here.

I've uploaded an excel document, which i've linked to below.

http://snk.to/f-chk9akpz (Password: MrExcel)

I'm trying to pull data from one sheet (project planner) that is displayed in another sheet (summary). But cant seem to work it out. If you open the attachment/link above, i've explained further on the 'Summary' tab.

Advice, or if you could demontrate how its done i'd really appreciate it.

I can be emailed at jeet.padaruth(at)eastlondon.nhs.uk - did that so i dont get spammed.

Many thanks >> Jeet.

 

[Jerry Sullivan]

Hi Jeet and Welcome to the Board,

Options for doing what you describe would depend on the range of input scenarios that would need to be handled.

Your example Workbook is understandably simple... with each Person having one task, each task having Person assigned, each task having one time period.

If in your actual use, there will be many to many relationships between these elements, then the problem becomes much more complex.

Please clarify a bit more the range of scenarios that the Planner will need to handle.

 

[PadaruthJ]

Hi Jeet and Welcome to the Board,

Options for doing what you describe would depend on the range of input scenarios that would need to be handled.

Your example Workbook is understandably simple... with each Person having one task, each task having Person assigned, each task having one time period.

If in your actual use, there will be many to many relationships between these elements, then the problem becomes much more complex.

Please clarify a bit more the range of scenarios that the Planner will need to handle.

Hi thanks for the warm welcome. I've a feeling im going to be back here more ofren then not!

There are going to be various 'keys' & 'activities' added. Also the date range for an individual may change i.e. put back but apart from that, it isnt that complex.

Whats your MVP opinion?

Thanks >> Jeet.

 

[Jerry Sullivan]

Jeet, I'd lean toward a VBA approach, because it might give your more options if/when your planning tool evolves.

If you'd like to go that route, I'd be glad to write some example code for you to try.

 

[PadaruthJ]

Hi Jerry

Apologies for the delay in responding, been hectic at work, actually this query (re project planner) came up in a meeting this morning :-/

I really appreciate your offer... if its ok with you, i'd love to try the VBA Approach.

How shall we proceed?

Again, very much appreciate your support.

Get back to me.

Cheers >> Jeet.

 

[Jerry Sullivan]

Jeet, Below is a macro that you could try. It assumes you first define a Named "MyData" that references the range of cells on the Planner sheet that are to be read.
For your example file, that would be A1:M22.

Paste into a Standard Code Module...

Sub UpdateSummary()
    Dim vData As Variant, vResults As Variant
    Dim lRow As Long, lCol As Long, lResultNbr As Long
    Dim sKey As String, sActivity As String
    
    '--read data into array
    vData = Sheets("Planner").Range("MyData").Value
    '--assumes first columns is Key,second: Activity,
    '     third to X are Date-Role assignments
    
    '--validate minimum size of array
    If UBound(vData, 1) < 2 Or UBound(vData, 2) < 3 Then
        MsgBox "Data range must have at least 2 rows and 3 columns"
        Exit Sub
    End If
    
    '--size array to hold maximum possible assignments
    ReDim vResults(1 To 4, 1 To _
        UBound(vData, 1) * (UBound(vData, 2) - 1))
    
    For lRow = 2 To UBound(vData, 1)
        For lCol = 3 To UBound(vData, 2)
            sKey = vData(lRow, 1)
            sActivity = vData(lRow, 2)
            If vData(lRow, lCol) <> "" Then
                lResultNbr = 1 + lResultNbr
                vResults(1, lResultNbr) = vData(lRow, lCol) 'date
                vResults(2, lResultNbr) = sKey
                vResults(3, lResultNbr) = sActivity
                vResults(4, lResultNbr) = vData(1, lCol) 'role
            End If
        Next lCol
    Next lRow
    
    '--resize array to match result rows
    ReDim Preserve vResults(1 To 4, 1 To lResultNbr)
    
    With Sheets("Summary")
    '--clear existing summary entries, if any
        .Range("A2:D" & .Rows.Count).ClearContents
    
    '--write results to range
        .Range("A2").Resize(lResultNbr, 4).Value = _
            Application.Transpose(vResults)
    End With

End Sub

This code doesn't include formatting of the Summary data. That could be added to the macro or handled through Conditional Formatting.