If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#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(C1Ratios!B1,"Red",if(C1Ratios!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(C1Ratios!$B$1,"Red",if(C1Ratios!$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(C1Ratios!B1,"Red",if(C1Ratios!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(C1Ratios!$B$1,"Red",if(C1Ratios!$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(C1Ratios!B1,"Red",if(C1Ratios!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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Changing excel query db location | Dana | General Discussion | 8 | August 20th, 2004 03:49 PM |
Changing location of CD install | Gordon | Setup, Installing & Configuration | 2 | July 26th, 2004 08:07 AM |
Changing Multiple Cells from Relative to Absolute Functions | Gord Dibben | Worksheet Functions | 1 | February 1st, 2004 09:18 PM |
Default File Location | Derek Ruesch | Setting up and Configuration | 6 | January 30th, 2004 03:03 AM |