Trouble with spreading a cell value based on start/end dates

[lisad]

Tracking manpower availability - here is my layout:

A1: Name: Lisa
A2: Start Date: 06-Nov (drop down)
A3: End Date: 27-Nov (drop down)
A4: Availability .25/.33/.50/.75/1.00 (drop down)

I would like to have the value of A4 drop in the cells automatically in a 4-month calendar like this on the same spreadsheet like this:

06-Nov|13-Nov|20-Nov|27-Nov|and so on...
.25---->.25---->.25---->.25

Is this possible?
Many thanks in advance!!

 

[Damon Ostrander]

Hi lisad,

Yes, this would not be difficult. But I am not sure whether you just want the availability value added to the already created calendar, or whether the calendar itself must be created based on the values in A2 and A3. Can you clarify?

Damon

 

[Damon Ostrander]

Hi again Lisa,

Okay, to add it to your already created calendar, the easiest way is to simply use formulas in the calendar cells to reference the availability value. Let's say in your example 06-Nov of the calendar is in cell C2 such that 27-Nov is in F2. Simply place the formula "=$A4" in cells C3:F3.

But if you want it to automatically adjust by re-generating the calendar each time you change A2 or A2, you need a bit of code. Here is code that will do this assuming the starting location is C2 as above.

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Address = "$A$2" Or Target.Address = "$A$3" Then
      'update calendar in cells C2 to Cn (n depending on end date)
      Range("C2", Cells(3, 255).End(xlToLeft)).ClearContents
      Dim D       As Date
      Dim iCol    As Integer
      iCol = 3    'Column C
      For D = Range("A2").Value To Range("A3").Value Step 7
         Cells(2, iCol) = D
         Cells(3, iCol).Formula = "=$A4"
         iCol = iCol + 1
      Next D
   End If
End Sub

This code must be placed in the worksheet's event code module. To do this, right-click on the worksheet's tab, select View Code, and paste this code into the Code pane. When you run this code the first time you should make sure that there is already something in at least C2 and C3.

Of course, this code will need to be modified a bit if your calendar doesn't start in C2.

Keep Excelling.

Damon