Excel Question - copying formulas

[BeefSandwiches]

I am no Excel-whiz, so any help is MUCH appreciated...

I created a workbook with worksheets for each position, etc and one sheet containing variables to be used in my projection equations. For example:

Sheet 1 (point values)
Stat 1 = 1 point (in cell B3)
Stat 2 = 2 point (in cell B4)
Stat 3 = 3 points (in cell B5)

Sheet 2 (projections)
PLAYER A
Stat 1 = 25 (in cell B3)
Stat 2 = 50 (in cell C3)
Stat 3 = 75 (in cell D3)

Here is the formula I have to calculate the projected total points for PLAYER A using Sheet 1's point values:

=(Sheet2!B3*Sheet1!B3)+(Sheet2!C3*Sheet1!B4)+(Sheet2!D3*Sheet1!B5)

How do I copy this formula for 100 other rows of players and keep the Sheet 1 cell references constant? How do I copy it across to other worksheets and keep the Sheet 1 cell references constant?

Here is the formula I get when I copy the cell to the next row:

=(Sheet2!B4*Sheet1!B4)+(Sheet2!C4*Sheet1!B5)+(Sheet2!D4*Sheet1!B6)

When the formula I want is:

=(Sheet2!B4*Sheet1!B3)+(Sheet2!C4*Sheet1!B4)+(Sheet2!D4*Sheet1!B4)

Or is there a more efficient way of calculating the values?

Thanks in advance.

[mbuser]

in a formula like this: A1*B1

if you want to keep A1 constant and fill the formula down for all the B's, you mark it as: $A$1

not sure if the same principle applies for multiple sheets, but you could give it a shot

[acsguitar]

God we need a tech support forum!! Come on mods make it happen!

[Amazinz]

in a formula like this: A1*B1

if you want to keep A1 constant and fill the formula down for all the B's, you mark it as: $A$1

not sure if the same principle applies for multiple sheets, but you could give it a shot

It does.

Like mbuser said, $ is use it to mark an absolute reference. You can use to mark the column, row or column+row absolute.

$A1 Column
A$1 Row
$A$1 Column+Row

[knapplc]

God we need a tech support forum!! Come on mods make it happen!

We almost need one dedicated solely to Excel. It's used by so many, and so few are "experts."

[acsguitar]

Sign my techie form petition!

[knapplc]

Sign my techie form petition!

Done!

[acsguitar]

Sign my techie form petition!

Done!

Thanks! I think it would be cool to have it and very helpful...we'll see

[Big Pimpin]

The only thing I can add is a couple of shortcuts. For instance, like Amazinz said, you can lock row, column, or both. So you could do the formula in the first cell, lock the row, and then copy that formula across as many columns as you want. Then you can copy it down and it will be how you want it.

Also, the easiest way to lock the reference is to click on the particular cell you want to lock and press F4 until you've got it like you want it (row, column, or specific cell).

And lastly, if you've got a list (say of players) on the left side of what you're doing, you can highlight the row of cells you want copied all the way down, go to the lower right-hand corner, and double click on that and it will copy as far down as your list (of players) goes. Hope that helps.