excel 2007 timeline, help pleaseSolved/Closed

Question

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)

Orkt · Answer

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

Excel 2007 timeline, help please

1 response

Similar discussions

Subscribe To Our Newsletter!