Excel 2007 timeline, help please [Solved/Closed]

Report
Posts
6
Registration date
Friday May 29, 2009
Status
Member
Last seen
June 22, 2009
-
Posts
6
Registration date
Friday May 29, 2009
Status
Member
Last seen
June 22, 2009
-
Hello,

timeline, help please
im looking for a formula to fill in a cell with either s,p,v or h depending on data taken from sheet 2. the data on sheet2 is in a column in the format of name,date,(s,p,v or h)-- hundreds of rows. The cell I want automaticaly filled in on sheet1 is actually a continuous timeline (of dates for 10 years).

so on sheet1 would look like this:
columns------------->
name, <---------timeline------10 years worth---s------s-----s-----ppp------v------etc....------->
where the spv or h are on the timeline in the correct spot according to the date taken from the list(column) on sheet2.

sheet2 is just:
name1,date,s-p-v or h,amount(1 or .5)
name2 ....
name3 etc...


the start date is always the same on sheet1, and date on sheet2 is always > start date
so date-startdate=offset from startdate where the spv or h's have to go.
hope that makes some sense. any help apprecitated.

also the names, there are "rows of 20" with the same timelines but different data (spv or h)

1 reply

Posts
6
Registration date
Friday May 29, 2009
Status
Member
Last seen
June 22, 2009
1
Sub ABC()

Dim sh1 As Worksheet, sh2 As Worksheet
Dim r1Date As Range, r2Date As Range
Dim r1Name As Range, r2Name As Range
Dim cell As Range, cell1 As Range, Cell2 As Range Set sh1 = Worksheets("Sheet1") Set sh2 = Worksheets("Sheet2") Set r1Date = sh1.Range(sh1.Cells(1, 2), sh1.Cells(1, Columns.Count).End(xlToLeft)) Set r1Name = sh1.Range(sh1.Cells(2, 1), sh1.Cells(Rows.Count, 1).End(xlUp))

Set r2Date = sh2.Range(sh2.Cells(1, 2), sh2.Cells(Rows.Count, 2).End(xlUp)) Set r2Name = r2Date.Offset(0, -1)

For Each cell In r1Date
For Each cell1 In r1Name
For Each Cell2 In r2Date
If cell = Cell2 Then
If Cell2.Offset(0, -1) = cell1 Then
sh1.Cells(cell1.Row, cell.Column).Value = Cell2.Offset(0, 1).Value
Exit For
End If
End If
Next Cell2
Next cell1
Next cell
End Sub