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

##### New Member
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?

### Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

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

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)
'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

Replies
7
Views
133
Replies
1
Views
378
Replies
11
Views
2K
Replies
8
Views
2K
Replies
7
Views
177

### Forum statistics

1,221,053
Messages
6,157,640
Members
451,426
Latest member
VinnyDoesntKnowExcelCode

### 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.

### Which adblocker are you using?

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

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