Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dragging an array?
Tom,
What can I say - excellent - and works a treat! Thanks for your help Tony |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
meaning of : IF(Switch; Average(array A, array B); array A) | Excel Worksheet Functions | |||
Dragging range (array) | Excel Discussion (Misc queries) | |||
About this dragging... | Excel Discussion (Misc queries) | |||
dragging | Excel Discussion (Misc queries) | |||
Dragging Across | Excel Discussion (Misc queries) |