Changing the array in a cell dependent on a check box

B

brooksc29

Active Member
Joined
Jul 25, 2010
Messages
333
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!
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Sort by date Sort by votes
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()))))
 
Upvote 0
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!!
 
Upvote 0
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

brooksc29 said:
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!!
Click to expand...
 
Upvote 0
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
 
Upvote 0
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?



brooksc29 said:
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
Click to expand...
 
Upvote 0
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
 
Upvote 0
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

brooksc29 said:
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
Click to expand...
 
Upvote 0
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()))))
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
link a VBA checkbox to a Cell to change value
Replies
6
Views
624
NoSparks
N
M
  • Solved
run formula based on cell value to the last row
Replies
5
Views
396
MMM_84
M
B
  • Solved
VBA to check if worksheet exists
Replies
8
Views
332
bdt
B
J
  • Question Question
Dependent dynamic list with offset/countif formula and name manager
Replies
1
Views
690
juliecooper255
J
I
  • Question Question
Unable to run VBA code
Replies
6
Views
138
RoryA
RoryA

Forum statistics

Threads
1,224,527
Messages
6,179,345
Members
452,907
Latest member
Roland Deschain

Share this page

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