Need formula for populating info to corresponding columns in separate sheet

[kAR87]

I'm not even sure where to begin with this but here is what I was asked to do:

[TABLE="width: 903"]
<colgroup><col span="2"><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 5"]"When a date is entered in column F and the "Execution/Monitoring in Construction" is selected from the drop down list in column D,[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 5"]the Column information for that row is populated to the corresponding column headings in the "CMI_PMT_Project In Progress" tab: [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 8"]Customer Name, Project #, Project Name, Project Lifecycle Phase, Contract Award / NTP Date, Substantial Completion Date, Project Revenue, Date Last Updated/Reviewed."

I've been researching and all that I've been able to find is info on VLOOKUP and array formulas. If either of these are correct I don't know how to apply them to my situation. I can provide additional information if needed!

Thank you!


[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[mumps]

It is always easier to help and test possible solutions if we could work with your actual file. Perhaps you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[kAR87]

Here is the link to the file.

https://www.dropbox.com/s/buj55gk7pbzp6u1/CMI for Kelci to work on 3-20-16.xlsx?dl=0

The detailed instructions are at the top of the page on the tab named "PLRAM Tool".

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet "PLRAM Tool" and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. First enter your data in all columns except column F then enter the date in column F and exit the cell. The date in column F must always be the last piece of data that is entered in each row because this entry triggers the macro.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("F:F")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    If Target.Offset(0, -2) = "Execution/Monitoring - In Construction" Then
        Range("A" & Target.Row & ":D" & Target.Row).Copy Sheets("CMI_PMT_Project In Progress").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
        Sheets("CMI_PMT_Project In Progress").Cells(Rows.Count, "N").End(xlUp).Offset(1, 0) = Target
        Sheets("CMI_PMT_Project In Progress").Cells(Rows.Count, "P").End(xlUp).Offset(1, 0) = Target.Offset(0, 2)
        Sheets("CMI_PMT_Project In Progress").Cells(Rows.Count, "R").End(xlUp).Offset(1, 0) = Target.Offset(0, 3)
        Sheets("CMI_PMT_Project In Progress").Cells(Rows.Count, "F").End(xlUp).Offset(1, 0) = Target.Offset(0, 5)
    End If
    Application.ScreenUpdating = True
End Sub

 

[kAR87]

Thanks for the info! When I right click on the PLRAM Tool there is no "View Code" option?

 

[mumps]

Are you right clicking the "PLRAM Tool" tab at the bottom of the spreadsheet?

 

[kAR87]

Yep, these are the options that show when I right click on "PLRAM Tool"

Insert Sheet
Delete
Rename
Move or Copy
Select all Sheets
Protect Sheets
Tab Color
Unhide
Hide

 

[mumps]

That is very strange. Let's try another approach. Do the following: Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the left hand pane, below Microsoft Excel Objects, double click the "PLRAM Tool" sheet name. By the way, what version of Excel are you using?

 

[kAR87]

I am using Excel for Mac 2011 Version 14.3.9

 

[kAR87]

By the way ALT F11 worked! Thank you so much for your help! Very appreciated!!