Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old April 14th 15, 09:09 PM
Junior Member
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 2
Default 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

  #2   Report Post  
Old April 14th 15, 10:02 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,866
Default Returning contents of a cell identified by two logic tests

Hi Steve,

Am Tue, 14 Apr 2015 21:09:20 +0100 schrieb SteveRH:

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


in G2 try:
=IFERROR(INDEX(B:B,MATCH("Milestone"&F2,$A$1:$A$10 0&$C$1:$C$100,0)),"")
and insert the array formula with CTRL+Shift+Enter and copy down.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
  #3   Report Post  
Old April 15th 15, 01:44 PM
Junior Member
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 2
Default

Thanks Claus, you are a lifesaver, the formula works brilliantly, I just need to spend some time learning the Index and Match functions now.

This has thrown up one further issue though, being that if there are 2 or 3 milestones in the same week I am currently only able to show the first one. I know I can paste your formula into an array of cells to obtain multiple results but that seems only to work if the results array is a vertical one. I could really do with returning the multiple results horizontally in columns G, H and I. Is there a genius solution to this?
  #4   Report Post  
Old April 15th 15, 02:20 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,866
Default Returning contents of a cell identified by two logic tests

Hi Steve,

Am Wed, 15 Apr 2015 13:44:38 +0100 schrieb SteveRH:

This has thrown up one further issue though, being that if there are 2
or 3 milestones in the same week I am currently only able to show the
first one. I know I can paste your formula into an array of cells to
obtain multiple results but that seems only to work if the results array
is a vertical one. I could really do with returning the multiple
results horizontally in columns G, H and I. Is there a genius solution
to this?


list all occurances of your weeks in column F.
Then try in G2:
=IFERROR(INDEX($B$1:$B$100,SMALL(IF(($A$1:$A$100=" Milestone")*($C$1:$C$100=F2),ROW($1:$100)),COUNTIF ($F$2:F2,F2))),"")
insert the array formula with CTRL+Shift+Enter and copy down.

Or look he
https://onedrive.live.com/?cid=9378A...121822A3%21326
for workbook "INDEX"


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Returning contents of a cell based upon the minimum value in a ran Shawn Excel Worksheet Functions 4 September 2nd 07 08:48 PM
Returning contents of a cell in another sheet in same workbook Annette[_2_] Excel Discussion (Misc queries) 1 March 14th 07 04:04 PM
Countif Using Multiple Logic Tests Carl Excel Worksheet Functions 3 June 4th 06 07:09 AM
Returning contents of a cell Dan Excel Worksheet Functions 0 March 26th 06 11:48 PM
Two Logic tests in an IF Statement timmadge Excel Worksheet Functions 2 February 7th 06 03:15 PM


All times are GMT +1. The time now is 10:56 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017