PDA

View Full Version : Solved: Array Formulas Using CTRL SHIFT ENTER



coliervile
03-28-2008, 05:11 AM
If you have a large range of cells that have array formulas in is there one easy way to use the CTRL SHIFT ENTER on all of the range in one fell swoop and not having to go through each cell individually to do this?

xluser2007
03-28-2008, 05:14 AM
Charlie,

Select the range, then hit F2 + Ctrl+Shift+Enter.

The F2 Activates the first cell of the range and the ctrl Shift Enter then applies the array formula across the slected range.

Also if you have a non array formula you can hit F2 + Ctrl + Enter to copy across a selected range.

Hope this helps.

Bob Phillips
03-28-2008, 05:18 AM
No don't do that, that turns it into a block array formula, and there is little you can do with afterwards, you are stuck with those formulae in those cells. It may also return different results depending upon the formula.

The best way to do it Charlie is to enter it in the first cell, array-enter it, and drag-copy it down.

xluser2007
03-28-2008, 05:23 AM
No don't do that, that turns it into a block array formula, and there is little you can do with afterwards, you are stuck with those formulae in those cells...

Thanks Bob, I assumed this was what OP required. I see your point though especially if the references need to change for the array formulae as you move down the cells.

coliervile
03-28-2008, 05:28 AM
Thanks xluser2007 for you reply. Does your suggestion work for a range that are on every other row if you highlight every other row and then hit F2 + Ctrl+Shift+Enter? Also does the reverse happen to remove the array brackets using F2 + Ctrl+Shift+Enter?

xluser2007
03-28-2008, 05:34 AM
No worries Charlie,

To my understanding the reverse doesn;t happen when you do F2 + Ctrl + Shft + Enter.

The best way is Bob's method, namely, get the first cell right in term of the Array formula and then use autofill to drag acroos (or simple Copy + paste), assuming your array formulas change as you drag across.

So if you want to remove brackets, just put in your non-array formula at thge start and drag-across. That would be the fastest way (or F2+Shift+Enter, if it is suitable).

HTH,

coliervile
03-28-2008, 05:47 AM
Thanks Bob for the heads up.

My arrays are on every other line...is ther a way around this or is it recommended to highlight each row seperately and use F2 + Ctrl + Shft + Enter?

Bob Phillips
03-28-2008, 05:57 AM
No, put the formula in the first, select the first and second, then copy-drag down.

coliervile
03-28-2008, 07:54 AM
Thanks for your help, I think I've got it with some adapting.