Remember Me?

#### Menu

#1
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
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
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
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
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
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
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
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
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
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?

 Thread Tools Search this Thread Search this Thread: Advanced Search Display Modes Linear Mode

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post RobHan Excel Worksheet Functions 1 February 16th 06 06:21 PM Tenacity Excel Worksheet Functions 9 December 3rd 05 06:30 AM almedrahe abdualmohsn ahmad Excel Discussion (Misc queries) 1 November 19th 05 07:32 PM Bill Excel Worksheet Functions 3 January 19th 05 10:59 AM iloveexcellllll Excel Worksheet Functions 0 January 11th 05 03:29 AM

All times are GMT +1. The time now is 04:06 PM.

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

# About Us

"It's about Microsoft Excel"

Copyright © 2017