Returning contents of a cell identified by two logic tests
The attached spreadsheet extract (please excuse the format, and why can't I upload the spreadsheet?!?)is a much smaller version of a live problem I can't fix on a much larger project planning spreadsheet.
In column B there is a list of activities, column C shows the week the activity is planned to occur, and column A shows whether the activity is a milestone activity or not. As per the example the week numbers cannot necessarily be arranged in numeric order.
I need a solution to allow me to automatically generate the content of column G. The logic being that where column A shows a milestone, the activity name should be dropped into column G where the week number in F matches that in C.
I have tried array formulas without success and am totally stuck for ideas.
Column A Column B Column C
Milestone Actvity Week
- Test 1 3
- test 2 2
- test 3 2
- test 4 3
Milestone test 5 1
- Test 6 3
Milestone Test 7 7
- Test 8 5
- Test 9 6
Milestone Test 10 4
Column F Column G
Week Milestone activity
1 test 5
2
3
4 Test 10
5
6
7 Test 7
Any advice/help would be greatly appreciated.
Thanks
|