Changing the array in a cell dependent on a check box

[brooksc29]

In this problem, I need to alternate the array in a cell where if one is check box is checked it changes the formula for the array in the cell?

I'd like to say in cell DN6

if the box AL is checked in cell CR2, then the array is
=INDEX(ALABS!$AI$2:$AI$181,MATCH(1,IF(ALABS!$R$2:$R$181=$DA6,1,"")),0)

if the box is checked in cell CR3, then the array is
=INDEX(ALABS!$BJ$2:$BJ$181,MATCH(1,IF(ALABS!$AS$2:$AS$181=$DA6,1,"")),0)

thanks for your help wizards!

 

[brooksc29]

I've tried this formula and if CR2 is checked, it works, but if CR3 is checked it doesn't...

=IF($CR$2,INDEX(ALABS!$AI$2:$AI$181,MATCH(1,IF(ALABS!$R$2:$R$181=$DA6,1,"")),IF($CR$3,INDEX(ALABS!$BJ$2:$BJ$181,MATCH(1,IF(ALABS!$AS$2:$AS$181=$DA6,1,"")),NA()))))

 

[brooksc29]

i thought about just changing it to IF CR2 is checked, the array is one thing, if it's not it's another.

I entered this formula for that:
=IF($CR$2,INDEX(ALABS!$AI$2:$AI$181,MATCH(1,IF(ALABS!$R$2:$R$181=$DA6,1,"")),INDEX(ALABS!$BJ$2:$BJ$181,MATCH(1,IF(ALABS!$AS$2:$AS$181=$DA6,1,"")))))

and again CR2 being checked works, but if it's not I simply get FALSE.

any help would be greatly appreciated!!

 

[GK039]

Try the cell references not the whole formula

if CR2 checked cell reference number 1 else cell reference number 2. where the cell references hold the two INDEX functions

i thought about just changing it to IF CR2 is checked, the array is one thing, if it's not it's another.

I entered this formula for that:
=IF($CR$2,INDEX(ALABS!$AI$2:$AI$181,MATCH(1,IF(ALABS!$R$2:$R$181=$DA6,1,"")),INDEX(ALABS!$BJ$2:$BJ$181,MATCH(1,IF(ALABS!$AS$2:$AS$181=$DA6,1,"")))))

and again CR2 being checked works, but if it's not I simply get FALSE.

any help would be greatly appreciated!!

 

[brooksc29]

not sure I understand correctly, but I think an issue with that is one cell box will not only control the array in DN6, but in DN6:DY190. So, all those cells will have one array if cell box 1 is checked and all those cells with have another array if cell box 2 is checked.

I was hoping to figure it out for DN6, then apply it across the row and drag it down to row 190

 

[GK039]

If i got it right from previous posts you want cell DN6 to hold something like

IF(check box is true, first array formula, second array formula) Correct?
Do you want all the cells to house the result of the same arrays?

not sure I understand correctly, but I think an issue with that is one cell box will not only control the array in DN6, but in DN6:DY190. So, all those cells will have one array if cell box 1 is checked and all those cells with have another array if cell box 2 is checked.

I was hoping to figure it out for DN6, then apply it across the row and drag it down to row 190

 

[brooksc29]

quote:

If i got it right from previous posts you want cell DN6 to hold something like

IF(check box is true, first array formula, second array formula) Correct?

YES!


Do you want all the cells to house the result of the same arrays?

sort of.
for instance the array in cell DN6 will be
=INDEX(ALABS!$AI$2:$AI$181,MATCH(1,IF(ALABS!$R$2:$R$181=$DA6,1,"")),0)
if box one is checked

and DN7 will be
=INDEX(ALABS!$AI$2:$AI$181,MATCH(1,IF(ALABS!$R$2:$R$181=$DA7,1,"")),0)
if box one is checked

 

[GK039]

If the check box is linked to cell DN5 then try:
For DN6:
IF(DN5,INDEX(ALABS!$AI$2:$AI$181,MATCH(1,IF(ALABS!$R$2:$R$181=$DA6,1,"")),0),0)

For DN7:
IF(NOT(DN5),INDEX(ALABS!$AI$2:$AI$181,MATCH(1,IF(ALABS!$R$2:$R$181=$DA7,1,"")),0),0)

So if the check box is checked only the result of the first array will be shown and vice-versa. If you want both DN6 and DN7 to house values then don't use the NOT function in the second formula

quote:

If i got it right from previous posts you want cell DN6 to hold something like

IF(check box is true, first array formula, second array formula) Correct?

YES!


Do you want all the cells to house the result of the same arrays?

sort of.
for instance the array in cell DN6 will be
=INDEX(ALABS!$AI$2:$AI$181,MATCH(1,IF(ALABS!$R$2:$R$181=$DA6,1,"")),0)
if box one is checked

and DN7 will be
=INDEX(ALABS!$AI$2:$AI$181,MATCH(1,IF(ALABS!$R$2:$R$181=$DA7,1,"")),0)
if box one is checked

 

[brooksc29]

i'm not sure I'm making this clear b/c I need both arrays in DN6, which are linked to the check box in CR2.

can i combine these two in DN6?

in DN6, this is what I need:

if CR2 is checked do first INDEX, if not do 2nd INDEX
=IF($CR$2,INDEX(ALABS!$AI$2:$AI$181,MATCH(1,IF(ALABS!$R$2:$R$181=$DA6,1,"")),IF(NOT($CR$2),INDEX(ALABS!$BJ$2:$BJ$181,MATCH(1,IF(ALABS!$AS$2:$AS$181=$DA6,1,"")),NA()))))

 

[brooksc29]

that exact formula still gets FALSE when CR2 is not checked.