Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Trying to add column headings to array formula
On Mar 3, 6:36*pm, Pierre wrote:
Located this code that would be perfect for us. *Only thing is, that it starts at cell A1 and leaves no room for column headings. {=INDEX($A$1:$B$11,SMALL(IF($A$1:$A$11=$A$13,ROW($ A$1:$A $11)),ROW(1:1)),2)} . So to insert column labels, I adjusted the formula which moves all to row 2 and it does not work. (Have adjusted the formula to: . {=INDEX($A$2:$B$12,SMALL(IF($A$2:$A$12=$A$14,ROW($ A$2:$A $12)),ROW(2:2)),2)} . Once it works, I aim to copy down, and retrieve as many records as are present in column B, given a column A input What am I missing that would allow to place this array elsewhere on the worksheet? Thanks for any ideas. Pierre Think I found it. needed to spend some more time with the built in help area, The rules for array formuas aren;t all that visible. These points will help: RULES FOR ENTERING AND CHANGING ARRAY FORMULAS: The primary rule for creating an array formula is worth repeating: Press CTRL+SHIFT+ENTER whenever you need to enter or edit an array formula. That rule applies to both single-cell and multi-cell formulas. Whenever you work with multi-cell formulas, you also need to follow these rules: You must select the range of cells to hold your results before you enter the formula. .. You cannot change the contents of an individual cell in an array formula. To try this, select cell E3 in the sample workbook and press DELETE. You can move or delete an entire array formula, but you cannot move or delete part of it. In other words, to shrink an array formula, you first delete the existing formula and then start over. Tip To delete an array formula, select the entire formula (for example, =C2:C11*D2:D11), press DELETE, and then press CTRL+SHIFT +ENTER. You cannot insert blank cells into or delete cells from a multi-cell array formula. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COLUMN() problem with array formula | Excel Worksheet Functions | |||
Worksheet has numeric column headings. Change to alpha headings? | Excel Discussion (Misc queries) | |||
Column headings to numbers and row headings to alphabets? | Excel Discussion (Misc queries) | |||
What is formula to repeat row at top when column headings are numb | Excel Discussion (Misc queries) | |||
Can I invert a table so row headings are now column headings etc | Excel Worksheet Functions |