Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old July 19th 06, 07:19 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 18
Default showing multiple results to a formula

I am looking up everything in a column that has a "1" in it,(meaning there is
something due this week), and listing it's corrisponding value in a list of
items. Problum, if there is more then one thing due that week it only lists
one and not the others. Any suggestions?

  #2   Report Post  
Old July 19th 06, 08:56 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 256
Default showing multiple results to a formula

Assuming that Column B is used to designate whether an item is due, and
Column C contains the corresponding values of interest, try the
following formula which needs to be confirmed with CONTROL+SHIFT+ENTER,
not just ENTER...

E2, copied down:

=IF(ROWS(E$2:E2)<=COUNTIF($B$2:$B$100,1),INDEX(C$2 :C$100,SMALL(IF($B$2:$B
$100=1,ROW($B$2:$B$100)-ROW(B$2)+1),ROWS(E$2:E2))),"")

Adjust the range accordingly.

Hope this helps!

In article ,
Robert Brown wrote:

I am looking up everything in a column that has a "1" in it,(meaning there is
something due this week), and listing it's corrisponding value in a list of
items. Problum, if there is more then one thing due that week it only lists
one and not the others. Any suggestions?

  #3   Report Post  
Old July 19th 06, 09:16 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 18
Default showing multiple results to a formula

What is the E2 stuff for?

"Domenic" wrote:

Assuming that Column B is used to designate whether an item is due, and
Column C contains the corresponding values of interest, try the
following formula which needs to be confirmed with CONTROL+SHIFT+ENTER,
not just ENTER...

E2, copied down:

=IF(ROWS(E$2:E2)<=COUNTIF($B$2:$B$100,1),INDEX(C$2 :C$100,SMALL(IF($B$2:$B
$100=1,ROW($B$2:$B$100)-ROW(B$2)+1),ROWS(E$2:E2))),"")

Adjust the range accordingly.

Hope this helps!

In article ,
Robert Brown wrote:

I am looking up everything in a column that has a "1" in it,(meaning there is
something due this week), and listing it's corrisponding value in a list of
items. Problum, if there is more then one thing due that week it only lists
one and not the others. Any suggestions?


  #4   Report Post  
Old July 19th 06, 09:39 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 256
Default showing multiple results to a formula

As an example, I chose to return the results in Column E, starting at
E2. As the formula is copied down the column, ROWS(E$2:E2) returns a
series of numbers (1, 2, 3, etc.). The IF statement, which includes
ROWS(E$2:E2)<=COUNTIF($B$2:$B$100,1), ensures that a cell remains blank
when there are no other records that meet the criteria.

Does this return the desired result?

In article ,
Robert Brown wrote:

What is the E2 stuff for?

"Domenic" wrote:

Assuming that Column B is used to designate whether an item is due, and
Column C contains the corresponding values of interest, try the
following formula which needs to be confirmed with CONTROL+SHIFT+ENTER,
not just ENTER...

E2, copied down:

=IF(ROWS(E$2:E2)<=COUNTIF($B$2:$B$100,1),INDEX(C$2 :C$100,SMALL(IF($B$2:$B
$100=1,ROW($B$2:$B$100)-ROW(B$2)+1),ROWS(E$2:E2))),"")

Adjust the range accordingly.

Hope this helps!

In article ,
Robert Brown wrote:

I am looking up everything in a column that has a "1" in it,(meaning
there is
something due this week), and listing it's corrisponding value in a list
of
items. Problum, if there is more then one thing due that week it only
lists
one and not the others. Any suggestions?


  #5   Report Post  
Old July 19th 06, 09:52 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 18
Default showing multiple results to a formula

Yeh that helps, i was hoping to get them in one cell, but i can manage,
unless you can figure it out.... THank you for your help though!

"Domenic" wrote:

As an example, I chose to return the results in Column E, starting at
E2. As the formula is copied down the column, ROWS(E$2:E2) returns a
series of numbers (1, 2, 3, etc.). The IF statement, which includes
ROWS(E$2:E2)<=COUNTIF($B$2:$B$100,1), ensures that a cell remains blank
when there are no other records that meet the criteria.

Does this return the desired result?

In article ,
Robert Brown wrote:

What is the E2 stuff for?

"Domenic" wrote:

Assuming that Column B is used to designate whether an item is due, and
Column C contains the corresponding values of interest, try the
following formula which needs to be confirmed with CONTROL+SHIFT+ENTER,
not just ENTER...

E2, copied down:

=IF(ROWS(E$2:E2)<=COUNTIF($B$2:$B$100,1),INDEX(C$2 :C$100,SMALL(IF($B$2:$B
$100=1,ROW($B$2:$B$100)-ROW(B$2)+1),ROWS(E$2:E2))),"")

Adjust the range accordingly.

Hope this helps!

In article ,
Robert Brown wrote:

I am looking up everything in a column that has a "1" in it,(meaning
there is
something due this week), and listing it's corrisponding value in a list
of
items. Problum, if there is more then one thing due that week it only
lists
one and not the others. Any suggestions?




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
Copying a formula to multiple worksheets RobHan Excel Worksheet Functions 1 February 16th 06 05:21 PM
Match then lookup Tenacity Excel Worksheet Functions 9 December 3rd 05 05:30 AM
abdualmohsn almedrahe abdualmohsn ahmad Excel Discussion (Misc queries) 1 November 19th 05 06:32 PM
Formula to return cell contents based on multiple conditions Bill Excel Worksheet Functions 3 January 19th 05 09:59 AM
Function Formula is displayed not results iloveexcellllll Excel Worksheet Functions 0 January 11th 05 02:29 AM


All times are GMT +1. The time now is 12:51 PM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017