Looking up data based on date criteria

Microsoft

Board Regular
Joined
Jun 16, 2016
Messages
108
Hi guys,

I'm a long time lurker and have a problem that I've been struggling with this week.

I work in programme management and assign resources to various projects.

I've attached images below of a test sheet to demonstrate what I'm trying to do. Essentially I would like to have the cells B2:G6 auto-populate with the project that resource is assigned to based on the dates in cells B1:G1. The date range of their assigned project can be found in the second image. So far I have tried an =MEDIAN formula to ascertain that the dates in B1:G1 fall between the dates in tab two. I've also tried to use a very complicated SUMPRODUCT formula which didn't end up working out.

Any help would be very much appreciated!

ktCfjxn.jpg


OW0LUgH.jpg
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Are you or would you consider VBA? I'm assuming that as long as the Month and Year are a match for the Roll On Date Month and Year, then retrieve the Project Name?

Jeff
 
Upvote 0
Are you or would you consider VBA? I'm assuming that as long as the Month and Year are a match for the Roll On Date Month and Year, then retrieve the Project Name?

Jeff

Thanks for your reply Jeff. I would definitely consider VBA but I am very inexperienced with it unfortunately! Yes, as long as the date in the top row of sheet 1 falls between the roll-on, roll-off dates of sheet 2 that would work. I have written a SUMPRODUCT formula but unfortunately I can't, for some reason, make it dependent upon the name of the resource as well as all of the other criteria I listed.

Any help would be greatly appreciated.
 
Upvote 0
A few reasons that may have contributed to no suggestions so far:

1. As well as describing the expected results, show them. So, in your Sheet1 manually enter the expected result.

2. One example isn't much to go on, it shows no variety of what else might happen. 4 or 5 would be better.

3. Images are not much use as they cannot be copied to our sheets for testing and most helpers are not here to practice their typing. :)
See my signature block below for better ways to provide sample data.

Here is one possible approach. It does rely on each name only appearing once in column A of Sheet2 (Another case of showing a bit more sample data may have given us a clue about that)
It also assumes those dates in row 1 of Sheet1 are actual dates, being the first of each month.

Excel Workbook
ABCD
1NameOnOffProject
2John1/09/201627/09/2016a
3Jill5/08/201615/01/2017b
Sheet2



Formula in B2 is copied across and down.

Excel Workbook
ABCDEFG
1Name1/09/20161/10/20161/11/20161/12/20161/01/20171/02/2017
2Johna
3Jack
4Jillbbbbb
Sheet1
 
Upvote 0
Peter, thank you so much for your help with the formula.

I'll be sure to post in a more helpful way in the future! Thanks again :)
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,927
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top