Assign formulas to entire page -Except the header rows

[whitebuffalofarm]

I have a nearly finished spreadsheet, and would like to assign the current formulas to the entire page, so that when i add data it will not have to be tinkered with.

OH, WAIT. I don't want the formulas to be in the top 2 rows. . . . is that difficult?

Also, since it might be pertinent, I would like to have that A-Z SORT bar/thingy that sits under the header rows. Does Excel do that? (like on a googledocs form).

 

[GlennUK]

When you say "would like to assign the current formulas to the entire page" ... what exactly do you mean?

 

[whitebuffalofarm]

Thanks. I mean that my formulas are applied to a cell, and if i add new data to the row below, i have to copy the formulas and then add the data.

I would like to have the formulas in every column (currently A3:A - Q3:Q) apply down the whole column, so new data was referenced without re-applying each time.

Excel Workbook
	B	C	D	E	F	G	H	I	J	K
1		DATE	NAME	PICKED	out	under	ENDED	out	under	SYMBOL
2
3	POSTED AT 3/20/2011 4:00:52 AM	3/20/2011
4	100ozRound picked SHAW to outperform.	3/20/2011	100ozRound	picked	out					SHA
5	mpfdLT ended outperform picks on KCI.	3/20/2011	mpfdLT				ended	out		KCI
Sheet1

Excel Workbook
	B	C	D	E	F	G	H	I	J	K
5	mpfdLT ended outperform picks on KCI.	3/20/2011	mpfdLT				ended	out		KCI
Excel 2007 Sheet1

Excel 2007

Cell Formulas
Range	Formula
C5	= IF(LEFT($B5, 6)="POSTED", MID($B5, 10, 10),C4)
D5	=IF(LEFT($B5, 6)="POSTED", "", LEFT($B5, FIND(" ",$B5)-1))
E5	=IF(LEFT($B5, 6)="POSTED", "", IF(ISNUMBER(SEARCH("picked",$B5)), "picked", ""))
F5	=IF(LEFT($B5, 6)="POSTED", "", IF($E5="picked", SUBSTITUTE(IF(MID($B5,FIND(" to ", $B5)+4,LEN($B5))="underperform.","","out"),"outperform","out"), ""))
G5	=IF(LEFT($B5, 6)="POSTED", "", IF($E5="picked", SUBSTITUTE(IF(MID($B5,FIND(" to ", $B5)+4,LEN($B5))="outperform.","","under"),"underperform","under"), ""))
H5	=IF(LEFT($B5, 6)="POSTED", "", IF(ISNUMBER(SEARCH("ended",$B5)), "ended", ""))
I5	=IF(LEFT($B5,6)="Posted ","",IF($H5="ended",SUBSTITUTE(IF(MID($B5,FIND(" ended ",$B5,1)+7,5)="under","","out"),"outpe","out"),""))
J5	=IF(LEFT($B5,6)="Posted ","",IF($H5="ended",SUBSTITUTE(IF(MID($B5,FIND(" ended ",$B5,1)+7,5)="under","under",""),"outpe",""),""))
K5	=IF(LEFT($B5, 6)="POSTED", "", IF($E5="picked", MID($B5, FIND("^",SUBSTITUTE($B5, " ", "^", 2))+1, 3), SUBSTITUTE(MID($B5,FIND(" on ", $B5)+4,LEN($B5)),".","")))

 

[GlennUK]

You only need to fill about 5 rows of formulas ... after a list of about 5 items Excel automatically fills in formulas when you type in more data on to the end of the list.