Excel Matrix

  • Thread starter Thread starter Harrison Tigrett
  • Start date Start date
H

Harrison Tigrett

Hi,
New to Excel and trying to figure out how to create a
formula with constant and changing variables. I am trying
to create a matrix where I do the following:

SUM=(CELL A SHEET 1)-((CELL A SHEET 2)*(CELL A CURRENT
SHEET)

I am trying then to expand the formula down the chart. The
first variable (CELL A SHEET 1)excel changes correctly to
the next cell (CELL B SHEET 1). The problem is that I do
not want the next two variables to change (CELL A SHEET 2)
and (CELL A CURRENT SHEET) - I need them as constants
through the entire copying of the formula down the column.
I do know that I can just put in the values for those two
variables but the actual variables themselves may change
and I want them to be the formulas - PLEASE HELP IF
POSSIBLE thanks,
HT
 
Just add a $ in front of the cell reference you want to bo constant. ie. if
you want to lock the formula on A1, enter $A$1 in the formula. I you want
to lock on the column only, but not the row, it would be $A1, or just the
row would beA$1.

Hth,
Dan
 
Harrison,

If you want a row or column reference to be absolute put a
$ in front of it (ie $A$1). Just so you know regular (ie A1)
references are called relative. For your example you need
to make CELL A SHEET 2 and CELL A CURRENT SHEET
absolute refences, while CELL A SHEET 1 remains relative.

Example if you were using cell A1 on all three sheets

=Sheet1!A1 - (Sheet2!$A$1*$A$1)

Then as you dragged down you'd get
=Sheet1!A2 - (Sheet2!$A$1*$A$1)
=Sheet1!A3 - (Sheet2!$A$1*$A$1)
=Sheet1!A4 - (Sheet2!$A$1*$A$1)
etc...

dragging across
=Sheet1!B1 - (Sheet2!$A$1*$A$1)
=Sheet1!C1 - (Sheet2!$A$1*$A$1)
=Sheet1!D1 - (Sheet2!$A$1*$A$1)

STYLE DRAG DOWN DRAG ACROSS
A1 A2 A3
$A1 $A2 $A2
A$1 A$1 B$1
$A$1 $A$1 $A$1

Dan E
 
Noticed my own stupidity!!!

STYLE DRAG DOWN DRAG ACROSS
A1 A2 B1
$A1 $A2 $A1
A$1 A$1 B$1
$A$1 $A$1 $A$1
 
Back
Top