Return values in sequence if TRUE

santosing

New Member
Joined
Jun 17, 2011
Messages
2
Hi everyone,



First post here many thanks in advance to those that are kindly able/willing to assist. I'm running Excel 2010 (Windows 7)

I'm trying to find the most efficient way to return a "sequence of TRUE values" that qualify with a TRUE checkbox condition from a range of 9 data values (C10 to C18)

I've setup 9 checkboxes linked to 9 different cells (AO10 to AO18) that display a TRUE/FALSE value when each respective checkbox is checked/unchecked respectively.

C10=Data A [TRUE] linked to cell AO10=FALSE
C11=Data B [TRUE] linked to cell AO11=TRUE
C12=Data C [TRUE] linked to cell AO12=TRUE
C13=Data D [FALSE] linked to cell AO13=FALSE
C14=Data E [TRUE] linked to cell AO14=TRUE
C15=Data F [FALSE] linked to cell AO15=FALSE
C16=Data G [TRUE] linked to cell AO16=TRUE
C17=Data H [TRUE] linked to cell AO17=TRUE
C18=Data I [FALSE] linked to cell AO18=FALSE

On a different sheet I'd like to display only the qualifying Data Values with a TRUE condition in sequence. In the above example, there are 5 TRUE conditions so I'd like to display 5 data values in sequence such as:

Data B
Data C
Data E
Data G
Data H

Further the checkboxes can occur in any combination. The above is only one example of numerous possibilities.

I imagine a complicated set of IF statements if setup properly could do the trick but it seems there must be a more streamlined solution by using a combination of MATCH / INDEX and SEQUENCING function?

Any help is certainly much appreciated.

thanks,
santosing
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Hi all,

Just responding to my own post. Sorry If my question didn't encourage responders. I probably didn't provide sufficient detail to do so and will learn for next time 'round.

I figured out an alternative and better solution than using a ton of nested if statements using a combination of MATCH AND INDIRECT functions

Note: AO10:AO18 contains the TRUE/FALSE data range

First, determine the row of the first true condition:
cellAP10=IFERROR(MATCH(TRUE,INDIRECT("AO"&MATCH(TRUE,$AO$10:$AO$18,0)+1&":AO18"),0)+MATCH(TRUE,$AO$10:$AO$18,0),"")

Next determine the row of the second true condition:
cellAP11=IFERROR(MATCH(TRUE,INDIRECT("$AO"&AP10+1&":$AO18"),0)+AP10,"")

Copy/paste formula down the range. IFERROR function basically displays blanks if the formula returns an invalid value.

With the sequential row numbers known you can then use VLOOKUP or INDEX to retrieve/display the information you are looking for.

thanks for viewing
 
Upvote 0

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

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