Advice on Approach to Convert List of Dates into a Calender format

[Andibevan]

Hi All,

I am after some advice on how to tackle a problem I regularly come up
against - I am currently updating this manually but it is quite time
consuming and open to errors. I have a list of dates of when certain events
will take place on the Source Data sheet. The data is formatted as
follows:-

Task ID Task Name Task Start Date Task Finish Date Location

The calender sheet then has the date listed in column A, and 1 column for
each Location. Each cell on the calender sheet then obviosly refers to a
specific location on a specific day. I essentially need to transfer the
information from the date list onto a calender so that the relevent task
name appears in the related date / location cell.

I have been trying to do this using a function that contains a Sumproduct
formula (using evaluate) but it is very complicated and seems a very hard
way to do it. Has anyone else had to do anything similar to this? Any
advice? I was trying to create a dynamic solution but the problem here will
be if any of the tasks overlap?

I have included my current code below just in case someone can spot my
mistake.

Thanks

Andi

Function DepCHRT()



Dim Var_WorkBK As String, Var_SheetZZ As String

Dim Var_ENV_ColNum As Integer, Var_ENV_ColLet As String

Dim Var_Date_Row As Integer

Dim strTask_Start As String, strTask_Finish As String, strTask_Type As

String

Dim str_Date As String, strTask_DCIT As String

Dim ColType As String, EvalSTR As String, str_Type As String



'Find Location of Function

Var_WorkBK = Application.Caller.Parent.Parent.Name 'Workbook

Var_SheetZZ = Application.Caller.Parent.Name 'Sheet

Var_ENV_ColNum = Application.Caller.Column 'Column

Var_Date_Row = Application.Caller.Row 'Row where date is

ColType = Col2Type(Var_ENV_ColNum) 'Column Type string

'ColType = """" & ColType & """"



'Convert Column number to Letter

If Var_ENV_ColNum > 26 Then

Var_ENV_ColLet = Chr(Int((Var_ENV_ColNum - 1) / 26) + 64) & _

Chr(((Var_ENV_ColNum - 1) Mod 26) + 65)

Else

' Columns A-Z

Var_ENV_ColLet = Chr(Var_ENV_ColNum + 64)

End If



'Strings for use in evaluate formula

strTask_Start = "'" & Var_WorkBK & "'!LU_DC_Start" 'Address of Task

Start

strTask_Finish = "'" & Var_WorkBK & "'!LU_DC_Finish" 'Address of Task

Finish

strTask_Type = "'" & Var_WorkBK & "'!LU_DC_Type" 'Address of Task

Type

strTask_DCIT = "'" & Var_WorkBK & "'!LU_DC_ID" 'Address of DC task

ID



str_Type = "'" & Var_SheetZZ & "'!$" & Var_ENV_ColLet & "$65536"

'Address of Cell's Date

str_Date = "'" & Var_SheetZZ & "'!$A$" & Var_Date_Row 'Address of Column

Type





'Evaluate Formula

EvalSTR = ("SUMPRODUCT((" & str_Date & ">=" & strTask_Start & ")" & _

"*(" & str_Date & "<=" & strTask_Finish & ")*" & _

"(""" & ColType & """=" & strTask_Type & ")*(" & strTask_DCIT & "))")



DepCHRT = Evaluate(EvalSTR)



Exit Function



Err: 'Error Handling

DepCHRT = "Error"

End Function