Copy/Paste without changing location references
 

In Excel 2003, I have a multiple worksheet spreadsheet that uses cells from
one worksheet to another worksheet. For example, Worksheet "Data" (tabname
="Data") has several columns of numeric data. Worksheet "Ratios"
(tabname="Ratios") has only TWO entries of numeric data, B1 and B2.
When I define a formula in "Data" referencing "Ratios" and then copy the
formula down the column in "Data" the referenced cells in "Ratios" get
incremented in the formula/statement I'm using. For example, my formula in
"Data" could say "=if(C1<Ratios!B1,"Red",if(C1<Ratios!B2,"Green","Y ellowl"))"
Now if I copy/paste this from the "Data" Worksheet, the Ratios!B1 and
Ratios!B2 get incremented for each row that I paste the calculation in the
"Data" Worksheet.
Is there a simple solution to KEEPING the reference cells in the "Ratios"
Worksheet as "Ratios!B1" and "Ratios!B2" as the formulas are copy/pasted?
That is, keep the references to B1 and B2 in the "Ratios".
Extrapolating this, how can I keep ALL components in formulas in a
copy/paste from incrementing?
TIA,
Tom

Your formula could be re-written like this

=if(C1<Ratios!$B$1,"Red",if(C1<Ratios!$B$2,"Green" ,"Yellowl"))

The '$' keeps the Column or Row reference that it preceeds constant. These
can be added manually or by using F4 when entering in the formula.



"Tom" wrote:

In Excel 2003, I have a multiple worksheet spreadsheet that uses cells from
one worksheet to another worksheet. For example, Worksheet "Data" (tabname
="Data") has several columns of numeric data. Worksheet "Ratios"
(tabname="Ratios") has only TWO entries of numeric data, B1 and B2.
When I define a formula in "Data" referencing "Ratios" and then copy the
formula down the column in "Data" the referenced cells in "Ratios" get
incremented in the formula/statement I'm using. For example, my formula in
"Data" could say "=if(C1<Ratios!B1,"Red",if(C1<Ratios!B2,"Green","Y ellowl"))"
Now if I copy/paste this from the "Data" Worksheet, the Ratios!B1 and
Ratios!B2 get incremented for each row that I paste the calculation in the
"Data" Worksheet.
Is there a simple solution to KEEPING the reference cells in the "Ratios"
Worksheet as "Ratios!B1" and "Ratios!B2" as the formulas are copy/pasted?
That is, keep the references to B1 and B2 in the "Ratios".
Extrapolating this, how can I keep ALL components in formulas in a
copy/paste from incrementing?
TIA,
Tom

Thanks for the input. I've never used the "$" approach! You're input is
greatly appreciated.

"Richard Reye" wrote:

Your formula could be re-written like this

=if(C1<Ratios!$B$1,"Red",if(C1<Ratios!$B$2,"Green" ,"Yellowl"))

The '$' keeps the Column or Row reference that it preceeds constant. These
can be added manually or by using F4 when entering in the formula.



"Tom" wrote:

In Excel 2003, I have a multiple worksheet spreadsheet that uses cells from
one worksheet to another worksheet. For example, Worksheet "Data" (tabname
="Data") has several columns of numeric data. Worksheet "Ratios"
(tabname="Ratios") has only TWO entries of numeric data, B1 and B2.
When I define a formula in "Data" referencing "Ratios" and then copy the
formula down the column in "Data" the referenced cells in "Ratios" get
incremented in the formula/statement I'm using. For example, my formula in
"Data" could say "=if(C1<Ratios!B1,"Red",if(C1<Ratios!B2,"Green","Y ellowl"))"
Now if I copy/paste this from the "Data" Worksheet, the Ratios!B1 and
Ratios!B2 get incremented for each row that I paste the calculation in the
"Data" Worksheet.
Is there a simple solution to KEEPING the reference cells in the "Ratios"
Worksheet as "Ratios!B1" and "Ratios!B2" as the formulas are copy/pasted?
That is, keep the references to B1 and B2 in the "Ratios".
Extrapolating this, how can I keep ALL components in formulas in a
copy/paste from incrementing?
TIA,
Tom