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

lisad

New Member
Joined
Sep 29, 2006
Messages
1
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!!
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
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

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
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.

Code:
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
 

Forum statistics

Threads
1,140,941
Messages
5,703,299
Members
421,289
Latest member
java

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top