## Excel Question - copying formulas

Moderator: Baseball Moderators

### Excel Question - copying formulas

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?

BeefSandwiches
Minor League Mentor

Posts: 611
Joined: 18 Mar 2004
Home Cafe: Football

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
mbuser
Major League Manager

Posts: 1197
(Past Year: 21)
Joined: 21 Feb 2004

God we need a tech support forum!! Come on mods make it happen!
I'm too lazy to make a sig at the moment
acsguitar
Hall of Fame Hero

Posts: 26722
Joined: 7 Apr 2004
Home Cafe: Baseball
Location: Back in General Talk WOOO!!!

mbuser wrote: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
Maine has a good swing for a pitcher but on anything that moves, he has no chance. And if it's a fastball, it has to be up in the zone. Basically, the pitcher has to hit his bat. - Mike Pelfrey
Amazinz
Mod in Retirement

Posts: 18800
Joined: 16 Mar 2004
Home Cafe: Baseball
Location: in Canada, toughening up figure skaters

acsguitar wrote: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."
Keep wreves in General Talk in 2011!
knapplc
Hall of Fame Hero

Posts: 7871
(Past Year: 1)
Joined: 27 Dec 2004
Home Cafe: Football
Location: It's "ell see." ELL SEE!!!

Sign my techie form petition!
I'm too lazy to make a sig at the moment
acsguitar
Hall of Fame Hero

Posts: 26722
Joined: 7 Apr 2004
Home Cafe: Baseball
Location: Back in General Talk WOOO!!!

acsguitar wrote:Sign my techie form petition!

Done!
Keep wreves in General Talk in 2011!
knapplc
Hall of Fame Hero

Posts: 7871
(Past Year: 1)
Joined: 27 Dec 2004
Home Cafe: Football
Location: It's "ell see." ELL SEE!!!

knapplc wrote:
acsguitar wrote:Sign my techie form petition!

Done!

Thanks! I think it would be cool to have it and very helpful...we'll see
I'm too lazy to make a sig at the moment
acsguitar
Hall of Fame Hero

Posts: 26722
Joined: 7 Apr 2004
Home Cafe: Baseball
Location: Back in General Talk WOOO!!!

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.
Big Pimpin
Mod in Retirement

Posts: 13715
(Past Year: 5)
Joined: 20 Apr 2005
Home Cafe: Baseball
Location: Building a metric. And being ignorable and stupid.