Multiple array

[Bugas]

I have lots of cells that contain formulas with arrays. How can I update these without:


clicking the cell
clicking the formula bar
then hitting CTRL+SHIFT+ENTER


I know you have to use CTRL+SHIFT+ENTER to "activate" the array.

For instance, my formula is =INDEX($AH$101:$AH$181;SMALL(IF($K$101:$K$181=K401;ROW($AH$101:$AH$181)-100);COUNTIF(K$401:K401;K401)))

If i don't click the cell, click the formula bar then hitting CTRL+SHIFT+ENTER I will get #value.

When I hit CTRL+SHIFT+ENTER my formula goes like this and works

{=INDEX($AH$101:$AH$181;SMALL(IF($K$101:$K$181=K401;ROW($AH$101:$AH$181)-100);COUNTIF(K$401:K401;K401)))}





I want to do it without using VBA.

Thanks

 

[Robert Mika]

I have lots of cells that contain formulas with arrays. How can I update these without:


clicking the cell
clicking the formula bar
then hitting CTRL+SHIFT+ENTER


I know you have to use CTRL+SHIFT+ENTER to "activate" the array.

For instance, my formula is =INDEX($AH$101:$AH$181;SMALL(IF($K$101:$K$181=K401;ROW($AH$101:$AH$181)-100);COUNTIF(K$401:K401;K401)))

If i don't click the cell, click the formula bar then hitting CTRL+SHIFT+ENTER I will get #value.

When I hit CTRL+SHIFT+ENTER my formula goes like this and works

{=INDEX($AH$101:$AH$181;SMALL(IF($K$101:$K$181=K401;ROW($AH$101:$AH$181)-100);COUNTIF(K$401:K401;K401)))}





I want to do it without using VBA.

Thanks

You can confirm once and the drag down as far as you need.

 

[T. Valko]

I have lots of cells that contain formulas with arrays. How can I update these without:


clicking the cell
clicking the formula bar
then hitting CTRL+SHIFT+ENTER


I know you have to use CTRL+SHIFT+ENTER to "activate" the array.

For instance, my formula is =INDEX($AH$101:$AH$181;SMALL(IF($K$101:$K$181=K401;ROW($AH$101:$AH$181)-100);COUNTIF(K$401:K401;K401)))

If i don't click the cell, click the formula bar then hitting CTRL+SHIFT+ENTER I will get #value.

When I hit CTRL+SHIFT+ENTER my formula goes like this and works

{=INDEX($AH$101:$AH$181;SMALL(IF($K$101:$K$181=K401;ROW($AH$101:$AH$181)-100);COUNTIF(K$401:K401;K401)))}





I want to do it without using VBA.

Thanks

Like Robert said, you array enter the formula** once then you can drag copy it.

Try it like this:

=INDEX($AH:$AH;SMALL(IF($K$101:$K$181=K401;ROW($AH$101:$AH$181));COUNTIF(K$401:K401;K401)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[Bugas]

Thank you all. It solved my problem

 

[T. Valko]

Thank you all. It solved my problem

Good deal. Thanks for the feedback!