Automatic formula when inserting new row.

[Commish]

I tried to find my answer and all I found was HOW HUGE this site is. You guys are amazing!
My challenge is in Microsoft Excel 2000 with WIndows XP.
I have a roster sheet that includes calculated stats.
In due time a team will have new players in a certain position which forces me to add a row in the midst of the spreadsheet (not at the bottom of the end list). I can insert the row but then there is a lack of formula. Is there a way to include the accurate formulas? (and perhaps I am just using the wrong formulas...very new at this concept, but self teaching....up until now!)
Let me try to paint the picture. I do apologize for the length, but I think the more you know the better you will understand.

Ranges: C6 to C23 are players names on the roster and listed to the right are columns with a variety of stats to calculate. (H, R, BI...etc).
Now..below the rosters (C40-U65536) are boxscores that will keep track of their daily activity (aka: boxscores).
In the roster range there are different formulas in each cell based on the corresponding stat category.
Example:
CELL F15 has: =SUMIF(C40:C65536,C15,F40:F65536)
CELL G15 has: =SUMIF(C40:C65536,C15,G40:G65536)
Column F refers to HITS
Column G refers to RUNS (...and so on...)

Ted Williams is listed in (CELL C15) and C40-C65536 is the range that Williams could possibly be in the lineup, and if in the lineup, it will calculate how many hits he collected (F40-F65536) and of course the OTHER stats listed to the right as well, with their formulas.

NOW....with THAT background information. I will be in need at some point to add a row to insert a new player at the position perhaps lets just say Ty Cobb is now needing to be in C15 (which then bumps Williams to C16) and then the entire row is now blank for 15, is there a way to have those formulas move down to row 16 for Williams and ALSO stay in row 15 for Cobb's stats to be calculated.
I HOPE THIS MAKES SENSE!
Thanks in advance for all assistance on this issue.

 

[Padawan]

Commish

Your post is a bit long, but I think this may work.

1. Highlight a range of cells you wish to copy; perhaps the row above where you want to insert the new row.
2. Ctrl-C to copy
3. Right Click on the row number (in the gray margin) where you want to insert your new cells/new row.
4. Left click on insert copied cells; Shift cells down.

I think this will accomplish what you want.

PS What state are you in?

 

[Commish]

JX~
I did apologize for its length, but I wanted to make sure it was understood.
I tried what you suggested and all I was getting was #REF! in every cell across the row I Inserted copied cells. You got me with Shift cells down?? I was not able to even do that before I was given the #REF! across the row.

What I am looking for, is a way if the row is inserted that the formula is automatically there to do its job. There will be other users with the spreadsheet, and if they need to add a player, they can do so with limited steps. Like (alt+I+R) and then type the new player is my intentions here. Is THAT possible?
By the way....From the FRIGID state of Illinois. Was I suppose to mention that in my post? (First time...did not know).
Sorry!
Thanks for the quick response!

 

[Padawan]

Long story. I thought you may have been my brother, but he's in Washington State. No, you don't have to tell us where you are at to make a post.

I suspect the error you are receiving is a difference between Excel 2000 and Excel 2003. 2003 allows you to insert a copied row, and it includes all of the formulas from the row you just copied.

I think the only to accomplish what you are trying to do would be to write it in VBA and use maybe a command button to insert you row. That way when the row inserts it could place your formulas in the appropriate cells.

This post will freshen up your chain and perhaps some other board members will have a better idea for you. If not, we can try again tomorrow.