Dragging an array?
 

Is there a fast and easy way to do this.
I am use the following array (provided by Tom Olgivy) to pick up the
contents of a cell, which returns it to another cell.

=OFFSET(Teams!$A$1,SMALL(IF(Teams!$C$5:$C$27="Play ing",ROW(Teams!$C$5:$C$27)),ROW(Teams!$A1))-1,4)

It works well but the problem is that I wish to extend the formula to
retrieve additional info in other columns and rows but the drag
selection does not see to work with it, i.e. I would like to drag the
formula across so the last col no changes from 4 to 5) and then I would
like to drag the formula down 11 rows where ',ROW(Teams!$A1))-1,5)'
increments like ,ROW(Teams!$A2))-1,5), ROW(Teams!$A3))-1,5),
ROW(Teams!$A4))-1,5) and so on.

I can get round it by typing it in but I need to apply this logic to a
large number of cells which use this formula, which means there is a
lot of work ahead. Is there an easier way of going about it?

Grateful for advice
Thanks
Tony

Dragging an array?
 

Solved it myself!
A friend suggested that why don't I create the formula first of all
(using the drag selection method) and then convert the formulas to
arrays. Done a quick in Google Groups and I found the following code
(credit to Chip Pearson) which works an absoulte treat.

Sub ConvertFormulasToArrays()
Dim Rng As Range
On Error Resume Next
For Each Rng In Selection.SpecialCells(xlCellTypeFormulas)
Rng.FormulaArray = Rng.Formula
Next Rng
End Sub

Dragging an array?
 

=OFFSET(Teams!$A$1,SMALL(IF(Teams!$C$5:$C$27="Play ing",ROW(Teams!$C$5:$C$27)
),ROW(Teams!$A1))-1,Column(Teams!D$1))

should do what you want.

If the source cell is array entered, dragging will maintain it as an array
formula.

--
Rgards,
Tom Ogilvy

wrote in message
oups.com...
Is there a fast and easy way to do this.
I am use the following array (provided by Tom Olgivy) to pick up the
contents of a cell, which returns it to another cell.


=OFFSET(Teams!$A$1,SMALL(IF(Teams!$C$5:$C$27="Play ing",ROW(Teams!$C$5:$C$27)
),ROW(Teams!$A1))-1,4)

It works well but the problem is that I wish to extend the formula to
retrieve additional info in other columns and rows but the drag
selection does not see to work with it, i.e. I would like to drag the
formula across so the last col no changes from 4 to 5) and then I would
like to drag the formula down 11 rows where ',ROW(Teams!$A1))-1,5)'
increments like ,ROW(Teams!$A2))-1,5), ROW(Teams!$A3))-1,5),
ROW(Teams!$A4))-1,5) and so on.

I can get round it by typing it in but I need to apply this logic to a
large number of cells which use this formula, which means there is a
lot of work ahead. Is there an easier way of going about it?

Grateful for advice
Thanks
Tony

Dragging an array?
 

Tom,
What can I say - excellent - and works a treat!
Thanks for your help

Tony