Excel Database Question

kfotedar · Jul 29th 2004

HI! I'm trying to create a project timeline in an ongoing database. The attached file has 2 tabs, data and summary.

The "data" tab has project phases going across columns and project manager names going down rows.

The "summary" tab has the timeline info as to who is doing what phase in which month.

I'm having a hard time getting it right. I thought pivot table would help but can't get all the data on the pivot table.

In "summary" tab cell $c$3 is a formula which I wrote which does the job right as the result in that cell is 1. But I need help when the result is more than 1 like in $g$4 of the "summary" tab.

Have a project deadline, any help will be appreciated.

Thanks,

Kavir

gollem · Jul 29th 2004

Hello,

I'm not sure what the problem is I've checked your file: c3 results 1 and g4 results 2, that's correct I think....
What's wrong with your method?

Do you want for example: 1 programming in ... and PM is ... and 1 programming in ... and PM is ...? (You want detailed information)

If this is the case I think you better use some Vba code instead of a formule.

Gollem

kfotedar · Jul 29th 2004

Gollem:

Well, I want the results in G4 to be descriptive as they are in C4, in terms of stating the phase and the name of the project manager. I don't know which formula to use when the result is >1, so that it can describe both in detail.

ANy help will be appreciated..

Kavir

kfotedar · Jul 30th 2004

To The OZ MVP's..guys I really need your help on this....

Maybe It cane be done by a Pivot table but I need some guidance on this one..

Thanks,

Kavir Fotedar

Robert B · Aug 3rd 2004

I arrived at a limited form of the results you want using the following code

Code

Sub MultiEntryCell()


    Dim myDate As Date
    Dim myPhase As String
    Dim myPM As String
    Dim myPM2 As String
    Dim myPM3 As String
    Dim myPM4 As String
    Dim myLocation As String
    Dim myLocation2 As String
    Dim myLocation3 As String
    Dim myLocation4 As String
    Dim myRange As Range
    Dim myRange2 As Range
    Dim i As Integer
    Dim j As Integer
    ActiveCell.FormulaR1C1 = "=COUNTIF(Data!RC:R[16]C,R[1]C)"
     j = ActiveCell
     
     i = 1
     ActiveCell.Offset(1, 0).Select
      
      myDate = ActiveCell
      
      myPhase = Range("A3")
    
    Range("C3").Select
        Set myRange = ActiveCell
   
    Sheets("Data").Select
    Range(myPhase).Select
    Selection.End(xlUp).Select
    Range(Selection, Selection.End(xlDown)).Select
    
    For i = 1 To j
    
    Selection.Find(what:=myDate, after:=ActiveCell, LookIn:=xlFormulas _
        , lookat:=xlPart, searchorder:=xlByRows, searchdirection:=xlNext, _
        MatchCase:=False).Activate       
        
If i = 1 Then
        Set myRange2 = ActiveCell
        ActiveCell.Offset(0, -2).Select
         myPM = ActiveCell
        ActiveCell.Offset(0, 1).Select
     myLocation = ActiveCell
     Application.Goto myRange2
     Range(Selection, Selection.End(xlDown)).Select
End If


If i = 2 Then
        Set myRange2 = ActiveCell
        ActiveCell.Offset(0, -2).Select
        myPM2 = ActiveCell
        ActiveCell.Offset(0, 1).Select
         myLocation2 = ActiveCell
        Application.Goto myRange2
     Range(Selection, Selection.End(xlDown)).Select
End If
 If i = 3 Then
        Set myRange2 = ActiveCell
        ActiveCell.Offset(0, -2).Select
         myPM3 = ActiveCell
        ActiveCell.Offset(0, 1).Select
         myLocation3 = ActiveCell
        Application.Goto myRange2
     Range(Selection, Selection.End(xlDown)).Select
End If
If i = 4 Then
        Set myRange2 = ActiveCell
        ActiveCell.Offset(0, -2).Select
         myPM4 = ActiveCell
        ActiveCell.Offset(0, 1).Select
         myLocation4 = ActiveCell
        Application.Goto myRange2
     Range(Selection, Selection.End(xlDown)).Select
End If
'i = i + 1
Next i
Application.Goto myRange
    
    ActiveCell = myPM & " " & "in" & " " & myLocation & Chr(10) _
    & myPM2 & " " & "in" & " " & myLocation2 & Chr(10) _
    & myPM3 & " " & "in" & " " & myLocation3 & Chr(10) _
   & myPM4 & " " & "in" & " " & myLocation4
  
End Sub

Display More

I have short cut the finding and allocating months and phases but maybe there is something you can use.

kfotedar · Aug 3rd 2004

Robert:

I tried, but it does not seem to work...maybe as I am not aware of which sheet to put the code in? maybe in a separate module? (am using excel 2000)

Thanks for your help.

Kavir

Robert B · Aug 4th 2004

Yes I suppose that was a vital bit of info. Start the macro in the cell above the first date on the Summary sheet. 2 other things, name/rename the Kickoff column in Data "Kickoff" ; I also changed some of the dates in the Data!Kickoff column to Jun-04 so the macro returned more than 1 PM.
As I said I have put some hard code into the macro and it applies only to Jun-04, but I it can be generalised and stuck inside a Do Loop.

Robert

Excel Database Question

Participate now!

Share