Return values in sequence if TRUE

[santosing]

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

 

[santosing]

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