Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
need to copy/paste formula w/o it changing | Excel Discussion (Misc queries) | |||
Changing bulk Link references to different workbook. | Excel Discussion (Misc queries) | |||
Add up Plus & Minus Figures separately by Location | Excel Worksheet Functions | |||
Changing cell references | Excel Discussion (Misc queries) | |||
Changing Cell References in Formulas | Excel Worksheet Functions |