Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 193
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
COLUMN() problem with array formula ejack Excel Worksheet Functions 0 April 24th 09 07:05 AM
Worksheet has numeric column headings. Change to alpha headings? Be Frank Excel Discussion (Misc queries) 1 June 18th 08 04:22 PM
Column headings to numbers and row headings to alphabets? Juliana Excel Discussion (Misc queries) 2 May 9th 08 05:58 PM
What is formula to repeat row at top when column headings are numb kcasillas Excel Discussion (Misc queries) 1 November 10th 05 02:17 AM
Can I invert a table so row headings are now column headings etc Sharon Excel Worksheet Functions 3 February 10th 05 08:28 PM


All times are GMT +1. The time now is 11:12 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"