Search : in
By :

Excel 2007 timeline, help please

Last answer on Jun 22, 2009 10:11:54 pm BST Orkt, on May 31, 2009 3:29:34 am BST 
 Report this message to moderators

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)

Configuration: Windows XP Internet Explorer 7.0

Best answers for « excel 2007 timeline, help please » in :
Avoid duplicates in Excel ShowAvoid duplicates in Excel In order to avoid duplication when encoding in a column from an excel sheet: take the conditional format on the first cell under the headings (eg A2) choose the following formula:...
[VBA: VB6] Using excel from another application Show[VBA: VB6] Using excel from another application Here is a little routine to call Excel from VB6 or another Office application. Paste in a general module (eg Module1) In VBA>> Insert>> Module and paste in the window ... In VB6>>...
Inserting an animated gif in Excel ShowInserting an animated gif in Excel To insert an animated gif image in an Excel spreadsheet, you must insert the image into a control. To insert the control, go to the View menu/Toolbars/Control Toolbox Activate the first button Design...
Spreadsheets - The Excel Interface ShowExcel Introduction Microsoft Excel is the spreadsheet in the Microsoft Office office suite. To start it, simply click on the appropriate icon in the Start menu (in Windows) or click on an Excel file (that has an .xls file extension). A Excel...
Ask your question Reply

1

 Orkt, on Jun 22, 2009 10:11:54 pm BST

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
   
Reply to Orkt

	

	
Ask your question Reply
They need your help