laurabrogan
New Member
Context:
I am using multiple worksheets of information to calculate levels of effort for projects by role and by month based on multiple variables:
1) size of project
2) role type
3) start date of project
Details of problem:
I am trying to populate a series of data spread across 6-23 periods (each period equals 1 month) in the appropriate month based on the start date of the project. The data is level of effort in hours being calculated in another worksheet. For example, in the attached sample spreadsheet, I am trying to take the calculated level of effort hours in columns F - AB and copy the data starting with period 1 (column F) in the appropriate month (columns AE - BH) based on the start date in column C. For example, with Project 1, the start date is 3/1/11 and I want the hours in period 1 (16 hours for Role 1) to be populated in Column AG (Mar 1, 2011), then period 2 (16 hours for Role 1) to be populated in Column AH (Apr 1, 2011), etc.
So far, I have tried IF statements that populate the appropriate month based on whether the start date is > or = to the month in Columns AE - BH. And, the results are inconsistent. And, since I am using cell references for periods 1-23, Project 2 is populating data starting in Aug 1, 2011 but with data from period 6, not period 1. I have also experimented with index and match formulas and have not had any luck making them work in this scenario.
Does anyone have any other suggested solutions?
http://rapidshare.com/files/451110675/dynamic_date_lookup.xlsx
I am using multiple worksheets of information to calculate levels of effort for projects by role and by month based on multiple variables:
1) size of project
2) role type
3) start date of project
Details of problem:
I am trying to populate a series of data spread across 6-23 periods (each period equals 1 month) in the appropriate month based on the start date of the project. The data is level of effort in hours being calculated in another worksheet. For example, in the attached sample spreadsheet, I am trying to take the calculated level of effort hours in columns F - AB and copy the data starting with period 1 (column F) in the appropriate month (columns AE - BH) based on the start date in column C. For example, with Project 1, the start date is 3/1/11 and I want the hours in period 1 (16 hours for Role 1) to be populated in Column AG (Mar 1, 2011), then period 2 (16 hours for Role 1) to be populated in Column AH (Apr 1, 2011), etc.
So far, I have tried IF statements that populate the appropriate month based on whether the start date is > or = to the month in Columns AE - BH. And, the results are inconsistent. And, since I am using cell references for periods 1-23, Project 2 is populating data starting in Aug 1, 2011 but with data from period 6, not period 1. I have also experimented with index and match formulas and have not had any luck making them work in this scenario.
Does anyone have any other suggested solutions?
http://rapidshare.com/files/451110675/dynamic_date_lookup.xlsx
